How to call pl/sql in unix script

Shell Programming and Scripting
1

sample code as following:

test_sql(){
#test#echo test_sql
str=`$ORACLE_BIN/sqlplus -s $user/$passwd <<EOM

    set verify off
    set heading off
    set feedback off

#--------start pl/sql
{
DECLARE
CURSOR pah_cs IS
select id from table
where letter = 'abcd';
temp number;
echo "-------"
BEGIN
OPEN pah_cs;
LOOP
FETCH pah_cs INTO temp;
EXIT WHEN pah_cs%NOTFOUND;
echo $temp > tt.csv 2>&1;
ND LOOP;
CLOSE pah_cs;
END;
}
exit
EOM`
}
test_sql

I want to use call pl/sql in unix script and put the output into another file and I want the file format is saving the output lilne by line.

but now it seems my pl/sql script hasnt been called at all.
Dunno what the problem is, anyone could help me?? many thanks.

2

Hi,

Of course it won't run .....

  1. Don't use shell commands inside the pl-sql block!
  2. After the "END" you should put "/".

It should be written as the following:

test_sql()
{
$ORACLE_HOME/bin/sqlplus -s $user/$passwd << EOM
set verify off
set heading off
set feedback off
DECLARE
CURSOR pah_cs IS
select id from table
where letter = 'abcd';
temp number;
BEGIN
OPEN pah_cs;
LOOP
FETCH pah_cs INTO temp;
EXIT WHEN pah_cs%NOTFOUND;
END LOOP;
CLOSE pah_cs;
END;
/
EOM
}
test_sql

Nir

3

hi,Nir

thanks for your response. good point but not enough.

the next problem is how I can put the 'temp' into a file. Such as if there are 5 records, whenever the cursor fetch one value then put it into a file,so that it would be 5 lines in the file.

many thanks.

4

Read this thread for a good way to accomplish your requirement. This works with either straight SQL or PL/SQL.

5

Unfortunately I think it is not what I want either.

now I am trying to use CLOB so that I could save the output in a file but still havent figured it out yet.

anyone got more idea about how to do it, just feel free to email me or post it here.

my email address is ***Deleted***.

many thanks.

6

just like this is okay.

sqlplus userid/passwd <<EOF >filename.log
your sql.
...

set serveroutput on; 
dbms_output.put_line(para1||' '||para2||...);


EOF

all output log write into log file.

7

hi, Thank you zealeS, more help.