Link to home
Start Free TrialLog in
Avatar of Caruso_eu
Caruso_eu

asked on

Oracle DBMS_SCHEDULER.create_program (running external program from PLSQL)

Hello,

Whta i want to do is run script in linux from my Oracle database procedure.

So far i did this script:
run_me.sh

php klients.php 1235584 linux_tt ccdev   < - those are parameters my php script takes

Open in new window



And with DBMS_SCHEDULER i have created a program/job:

begin
dbms_scheduler.create_job(
        job_name        => 'test_run_sh',
        job_type        => 'EXECUTABLE',
        job_action      => '/oracle/oracle_scripts/php_scripts/run_me.sh',
        start_date      => null,
        repeat_interval => null,
        enabled         => true,
        auto_drop       => FALSE,
        comments        => 'PDF test');
END;
/ 

Open in new window


I execute that job with:

exec dbms_scheduler.run_job('test_run_sh');

Open in new window


After i execute it i get error:

ORA-27369: job of type EXECUTABLE failed with exit code: 274670
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
27369. 00000 -  "job of type EXECUTABLE failed with exit code: %s"
*Cause:    A problem was encountered while running a job of type EXECUTABLE.
           The cause of the actual problem is identified by the exit code.
*Action:   Correct the cause of the exit code and reschedule the job. 

Open in new window


Thank you for your help!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>> those are parameters my php script takes

I don't see where you specified the parameters.  My guess is the shell script generated an error that was passed back to Oracle.

Try adding the necessary parameters:
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE

http://dbaspot.com/oracle-server/65458-help-getting-executable-run-under-dbms_scheduler.html
Avatar of Caruso_eu

ASKER

>> those are parameters my php script takes

Hello,

i meant parameters are those "hard coded" in script ... as 1235584 linux_tt ...

Running that script by hand executes just fine.
Apologizes.  I missed that.

Make sure the oracle user has execute on the file.

You might also need to explicitly set the environment variables inside the script itself.  When you run the script it uses your environment.  When oracle executes it, it has no environment variables.  If the php programs needs any, you need to set them inside the script.

Post the results of:
ls -al /oracle/oracle_scripts/php_scripts/run_me.sh
To bad i am at home already and have no access to linux at my work.

But to comment:

- excecute --- i have set it on script and have set it to directory object in oracle db.
-- i am aware of the variables ... thats why i have put my full path tp php and to script. (the one in my origina post is a bit outtdated).

will do more tests tomorrow and will post back!

Thank you for today!
Today i have rewrote my script ... i am not sure if i got all the variables correct but it works when u run it manually.

#!/bin/bash
#

export PATH=$PATH:/usr/local/bin
ORAENV_ASK=NO
#export ORAENV_ASK
ORACLE_SID=test
export ORACLE_SID
. oraenv
PATH=${PATH}:${ORACLE_HOME}/bin
export PATH
LOG_FILE=/oracle/oracle_scripts/php_scripts/php.log
#
############## End of Variables settings ###################### 
rm -f $LOG_FILE.tmp
mv -f $LOG_FILE $LOG_FILE.tmp
#

/usr/bin/php /oracle/oracle_scripts/php_scripts/clients.php 12154845 linux_tt test >> $LOG_FILE 2>&1

Open in new window


About letting user to have execute on file:

Post the results of:
ls -al /oracle/oracle_scripts/php_scripts/run_me.sh

here it is:

-rwxrwxrwx 1 oracle oinstall 447 Sep 19 06:41 /oracle/oracle_scripts/php_scripts/run_me.sh
Update:

Ok i have tested with removing execute privileges on script and i get a different error

ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied

Open in new window


... so file is found and oracle tries to run it. That part is solved.

I think that the problem now lies with env. variables like it was pointed out before.

So how do i set them corectlly?  (i am new to linux so i have basicaly no idea what am i doing )-

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello,

i have solved issue. Everything was fine except that i was tring to run script from user that didn't have right privileges (i know stupid me).

Thank you all for trying to help!
I've requested that this question be closed as follows:

Accepted answer: 250 points for slightwv's comment #a38413323
Assisted answer: 250 points for slightwv's comment #a38409398
Assisted answer: 0 points for Caruso_eu's comment #a38413636

for the following reason:

Self-solved the problem.
??