?
Solved

Oracle DBMS_SCHEDULER.create_program (running external program from PLSQL)

Posted on 2012-09-18
10
Medium Priority
?
1,655 Views
Last Modified: 2012-09-19
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!
0
Comment
Question by:Caruso_eu
  • 7
  • 3
10 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38409287
>> 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
0
 

Author Comment

by:Caruso_eu
ID: 38409314
>> 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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38409398
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Caruso_eu
ID: 38409418
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!
0
 

Author Comment

by:Caruso_eu
ID: 38412380
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
0
 

Author Comment

by:Caruso_eu
ID: 38412404
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!
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38413323
The variables look fine.  Just want to mention that this line is not necessary:
PATH=${PATH}:${ORACLE_HOME}/bin


The oraenv script will set the PATH for you.

Are you still getting an error after setting the env variables?
0
 

Author Comment

by:Caruso_eu
ID: 38413636
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!
0
 

Author Comment

by:Caruso_eu
ID: 38413651
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.
0
 

Author Comment

by:Caruso_eu
ID: 38413646
??
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question