Processing values from Oracle procedure

Hi all,

My oracle procedure returns more than one value.

i need to get one value at a time upto ending value ina shell script.

Please help me.....

Advice for forum posts, general:

To obtain the best answers quickly for processing datasets --
extracting, transforming, filtering, you should, after having
searched for answers (man pages, Google, etc.):

1. Post representative samples of your data (i.e.  data that
should "succeed" and data that should "fail")

2. Post what you expect the results to be, in addition to
describing them.  Be clear about how the results are to be
obtained, e.g.  "add field 2 from file1 to field 3 from file2",
"delete all lines that contain 'possum', etc. 

3. Post what you have attempted to do so far.  Post scripts,
programs, etc.  within CODE tags.  If you have a specific
question about an error, please post the shortest example of the
code, script, etc. that exhibits the problem. 

4. Place the data and expected output within CODE tags, so that
they are more easily readable. 

Special cases, exceptions, etc., are very important to include
in the samples.

--
Thanks to drl

1 Like

Consider the following code:

sqlplus -s <<! | while IFS= read -r; do printf 'argument: %s\n' "$REPLY"; done
/ as sysdba
set pages 0 feed off
select rownum from dual connect by rownum <= 10;
!

Or, if you don't like it all on one line:

sqlplus -s <<! | 
/ as sysdba
set pages 0 feed off
select rownum from dual connect by rownum <= 10;
!
while IFS= read -r; do 
  printf 'argument: %s\n' "$REPLY" 
done

It produces:

argument:        1
argument:        2
argument:        3
argument:        4
argument:        5
argument:        6
argument:        7
argument:        8
argument:        9
argument:       10

Substitute printf with whatever you need to do.
Note however:

  • if your shell complains about the missing varname after read, use read your_varname
  • if your shell complains about the -r switch, drop it

Procedure_name('From_date', 'To_date')
Result is:

20130101
20130102
20130103
20130104
.
.
.
..
20130131

I need to run another shell script using the above result one by one value

shell_test.sh 20130101
shell_test.sh 20130102
shell_test.sh 20130103
shell_test.sh 20130104
.
.
.
.
.
.
shell_test.sh 20130131
exit

Try:

sqlplus -s <<! |
username/password
set pages 0 feed off
exec procedure_name('From_date', 'To_date')
!
while read; do
  shell_test.sh "$REPLY"
done

Hi radoulov,

your code is works fine but i have a small doubt about this..

How to run plsql code, like
i want to save plsql code in a file like "driver.sql" the parameters of the code is from_date and to_date
I want to execute this script like your code

sqlplus -s <<! |
username/password
set pages 0 feed off
exec procedure_name('From_date', 'To_date')
insted of above line i need to execute plsql code and all the below process is same
!
while read; do
  shell_test.sh "$REPLY"
done

Please help me...

Get the procedure code from table: ALL_SOURCE

select text from all_source where name='PROCEDURENAME' order by line;

Save it in a file: driver.sql and run it.

I suppose pmreddy is asking for:

sqlplus -s <<! |
username/password
set pages 0 feed off
@driver.sql
!
while read; do
  shell_test.sh "$REPLY"
done
sqlplus -s <<! |
username/password
set pages 0 feed off
@driver.sql
!
while read; do
  shell_test1.sh "$REPLY"
  shell_test2.sh "$REPLY"
  shell_test3.sh "$REPLY"
done

my driver.sql returns more than 100 rows.
I need to run the shell scripts with in the while loop using the above result one by one values.
If any process is wrong need to stop the process.
ex: if shell_test2.sh fails need to stop the process no need to go next process

Thanks

[...]
while read; do
  shell_test.sh "$REPLY" || break
done