Need sample of how to run native linux shell script with plsql procedure.
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;
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.
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?
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