Home » SQL & PL/SQL » SQL & PL/SQL » Calling shell scripts from dbms_scheduler (Oracle 12.1.0.2.0, Red Hat 7.6)
 Calling shell scripts from dbms_scheduler [message #686460] |
Wed, 21 September 2022 07:48  |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Hi,
long time no see
I've got an issue trying to use dbms_scheduler to run liunx shell scripts on the OS
Here's some setup for a script in the DB:
CREATE table bob (a varchar2(100));
#!/bin/bash
echo "TEST"
sqlplus -s <user>/<pass> <<EOF
begin
insert into bob (a) values ('TESTED');
COMMIT;
end;
/
exit;
EOF
echo "************************************"
I created a directory called training_scripts in the oracle user home directory and stuck file in there.
I then tried running the following to run it:
begin
dbms_scheduler.create_job(
job_name => 'my_external_job',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/training_scripts/test_script.sh',
auto_drop => false,
enabled => true);
end;
/
begin
dbms_scheduler.run_job( 'my_external_job');
end;
/
ORA-27369: job of type EXECUTABLE failed with exit code: 274666 Oracle Scheduler error: Cannot access or open configuration file.?6
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 2
Unfortunately I've got some customers on oracle 12 - 12.1.0.2.0 (yes, I know, I would love to upgrade them, at some point it'll happen, but not soon enough for my issue).
Everything I've found implies those instructions should work on 12 as well.
Short hand version of what I did from the note instructions:
cd $ORACLE_HOME/rdbms/admin
sudo vi externaljob.ora
change run_user to oracle, run_group to dba
save file
cd $ORACLE_HOME/bin
sudo chown root extjob
sudo chmod 4750 extjob
OS version for oracle 12 is Linux Red Hat 7.6 (19 is Linux Red Hat 8.4)
Any idea what I'm missing?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Calling shell scripts from dbms_scheduler [message #686469 is a reply to message #686468] |
Wed, 21 September 2022 09:57  |
John Watson
Messages: 8976
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, it looks fine, doesn't it. I have only two more suggestions, neither of which strikes me as being relevant.
First, I used to create external jobs by specifying the shell as the executable and the script as an argument. The idea is that a script isn't actuallly on executable file. So like this:begin
dbms_scheduler.create_job (job_name => 'myjob',
job_type => 'executable',
job_action => '/bin/sh',
number_of_arguments => 1,
auto_drop => true);
dbms_scheduler.set_job_argument_value ('myjob', 1, '/home/oracle/script.sh');
dbms_scheduler.run_job ('myjob');
end;
/
|
|
|
|
| Re: Calling shell scripts from dbms_scheduler [message #686470 is a reply to message #686469] |
Wed, 21 September 2022 10:56 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
first - makes no difference
second - don't actually know the oracle password (use sudo for everything) so I created a credential for my application user using:
BEGIN
dbms_scheduler.create_credential(
credential_name => 'MY_OS_CREDS',
username => '<username>',
password => '<password>');
END;
/
begin
dbms_scheduler.create_job(
job_name => 'my_external_job',
job_type => 'EXECUTABLE',
job_action => '/home/<app user>/training_scripts/test_script.sh',
auto_drop => false,
credential_name => 'MY_OS_CREDS',
enabled => true);
end;
/
begin
dbms_scheduler.run_job( 'my_external_job');
end;
/
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing login executable failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sjseccel 1
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 2
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Calling shell scripts from dbms_scheduler [message #686479 is a reply to message #686477] |
Thu, 22 September 2022 10:20 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Right, in the meantime I've come up with an alternative approach - used java inside the DB to run my scripts, and that works on all oracle versions I've tried.
So cheers for the help but I'm good. That said, if anyone does know the solution I'm still curious (though my money says it's a bug and I need a patch).
EDITED TO ADD:
so for clarity the dbms_scheduler approach still isn't working in 12.1 and I still need solution for that version <sighs> but my java stored procedure does the job
[Updated on: Thu, 22 September 2022 10:22] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Apr 28 23:32:05 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
