Query Oracle tables and return values to shell script that calls the query

Shell Programming and Scripting
1

Hi,

I have a requirement as below which needs to be done viz UNIX shell script

(1) I have to connect to an Oracle database
(2) Exexute "SELECT field_status from table 1" query on one of the tables.
(3) Based on the result that I get from point (2), I have to update another table in the same oracle database. (eg., UPDATE table2 set column1='ABC' if the query in point2 retuns "MANUAL", if the query in point2 returns some other value for field_status then do not do anything on table2).

Could someone explain me how to acheive this ?? I Know how can this be acheived from UNIX script if the tables are in DB2 database but not sure how to write a UNIX script if tables are in Oracle database.

Thanks
Saaya

2

Hi.

I'm not sure you need a shell script to do this, when you can do it in SQL / PL/SQL.

$ . oraenv
ORACLE_SID = [DB1] ? DB1

$ sqlplus user/pass

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Oct 18 11:48:34 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options


SQL> create table A( F1 varchar2(32) )

Table created.

SQL> create table B( F2 varchar2(32) )

Table created.

SQL> insert into A values ( 'MANUAL' );

1 row created.

SQL> insert into A values ( 'SOME OTHER VALUE' );

1 row created.

SQL> commit;

Commit complete.

test.sql
================================
declare
  cursor MYCURSOR is select F1 from A;
begin
  for MYREC in MYCURSOR loop
    if ( MYREC.F1 = 'MANUAL' ) then
      insert into B( F2 ) values ( 'ABC' );
    end if;
  end loop;
end;
/
================================

$ sqlplus user/pass @test

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Oct 18 11:47:58 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL> 
PL/SQL procedure successfully completed.


$ sqlplus user/pass

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Oct 18 11:48:34 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options


SQL> select * from B;

F2
--------------------------------
ABC
3

Thanks for the response....Yeah that is true.. I can easily do this in PL/SQl, but the requirement I hvae here is to do this viz UNIX shell script.. Hence I was checking how to get the same using shell script..

4

In which case you need to qualify your select statement, or your question.

What if your select returns more than one record?

X=$(sqlplus -s user/pass << !
set heading off
set feedback off
select count(1) from A where F1 = 'MANUAL';
!)

echo $X

1


if [ $X -gt 0 ]; then
  sqlplus ......
  insert...
fi
5

ok.. I just gave an example SELECT statement. My actual select statement has WHERE Clause which makes sure that only one row is returned at any point in time.

6

Hi.

It helps if you post examples representative to your problem. This saves on the guess work.

My previous post shows how to return values from SQL/Plus into a shell variable.

If that's not enough, please do let me know.

7

Hi All,
i need a script to select record from oracle and send mail
there are multiple records in the table i need to read row by row and send mail
pls help