Toad World® Forums

run linux script using plsql


#1

Need sample of how to run native linux shell script with plsql procedure.


#2

Create a script file, and make it executable. In the file should be something like

sqlplus user/pass@instance @file_to_execute

In file_to_execute.sql

BEGIN

procedure_to_execute;

COMMIT;

END;


#3

I am trying to run a linux bash script on the database server. The following was the suggested code. However I am unable to get it to work.

BEGIN

DBMS_SCHEDULER.create_job (

job_name => ‘EXT_LOAD’

,job_type => ‘EXECUTABLE’

,job_action => ‘/bin/bash /ducsy3/oradata/datapump/HCM_ODS_CSV/MKTARBT.sh 666’

,number_of_arguments => 0

,start_date => SYSTIMESTAMP

,enabled => TRUE

,auto_drop => TRUE

,comments => ‘SQL*Loader Job Demo’);

END;

/

It may be a configuration or setup issue.


#4

Configuration or setup is a good start. You need to verify that the oracle user has access to those locations.

Why the reference to /bin/bash? Have you tried not referencing it? See this stackoverflow.com/…/run-shell-script-within-oracle-procedure

Also, you are passing parameter, i assume that you .sh is ready for it. Do you have any error messages?

Do you need to dynamically create the run time? What about just using linux cron to schedule the script?


#5

Create bash script like:

#!/usr/bin/bash

sqlplus -s ‘/ as sysdba’ <<EOF!

conn user/userpwd@db;

BEGIN

procedure_to_execute;

END;

/

exit

EOF!

and do not place real user and password in “sqlplus -s ‘/ as sysdba’” because ps -ef shows that value. Also this may be replaced with "sqlplus -s ‘/nolog’ as well.

Brg

dami