Hello,
I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea?
Here is idea about the code:
my shell script is:
-----------------------------------------------------------------------
#!/bin/bash
OUTPUT=$(sqlplus -s '/ as sysdba' <<-EOF
set heading off feedback off serveroutput on trimout on pagesize 0
select instance_name from v\$instance;
select version from v\$instance;
declare
i number := 1;
a NUMBER(38);
b NUMBER(38);
c VARCHAR(100);
begin
while i < 4 loop
select student_id, studentaccount_id, studentaddress into a,b,c from studenttable WHERE row_id = i ;
dbms_output.put_line(a);
dbms_output.put_line(b);
dbms_output.put_line(c);
--- here i would like to call another shell script stud.sh to use the values a,b and c.
i := i+1;
end loop;
end;
/
EOF
)
INSTANCE=$(echo $OUTPUT | awk '{ print $1 }')
VERSION=$(echo $OUTPUT | awk '{ print $2 }')
ARG1=$(echo $OUTPUT | awk '{ print $3 }')
ARG2=$(echo $OUTPUT | awk '{ print $4 }')
ARG3=$(echo $OUTPUT | awk '{ print $5 }')
echo "Database: $INSTANCE"
echo "Version: $VERSION"
echo "Arg1: $ARG1"
echo "Arg2: $ARG2"
echo "Arg3: $ARG3"
--------------------------------------------------------------------------------------------------
I manage to make the shell script read it after the loop finish, but my table is too big, i want to call the stud.sh script each time the loop is searching. and how to make the other shell script read my values.
Thanks a lot, i appreciate your help.