call shell script from pl/sql block

Shell Programming and Scripting
1

Hi Experts,

I want to call script_name.ksh as many time as id in customer table and also pass it as a parameter to script.
someting Like below.

 
for i in select id from customer
do
./script_name.ksh $i &
done

I have figured out how to have ID from customer but now how to call shell script within for loop. Kindly help.

  
 sqlplus -s $ORA_CONN <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool abc.txt
BEGIN   
FOR i IN (SELECT ID FROM customer)   
LOOP      
DBMS_OUTPUT.put_line ( 'value ' || i.ID);           
END LOOP;
END;
/
spool off
EOF
http://fedora.unix.com/images/misc/progress.gif

Now how to call script script_name.ksh within it.
Kindly help ASAP.

2

As you have spooled the result set to abc.txt. Use this file in the loop to run the script.

while read i
do
	./script_name.ksh $i &
	# sleep 5 # since your calling the same script, recommend sleep for few seconds
done < abc.txt
3

Thanks but this one I know.
I want to know how to call the script in pl/sql block.
I think there is a package dbms_sceduler.createprogram which can accomplish this. But I want to know full description of it.

4

In Oracle 10g and later you can use the dbms_scheduler package to execute external programs. In earlier versions this was done using dbms_java or dbms_pipe.
A full description can be found in the Oracle Documentation.

1 Like