| DBMS_SCHEDULER and perl script [message #625852] |
Wed, 15 October 2014 01:26  |
 |
rrk1
Messages: 4
Registered: October 2014
|
Junior Member |
|
|
Hi all,
I had written a script in Korn shell which called 2 pl/sql procedures. This shell script was called from the DBMS_SCHEDULER and it worked fine. But later I was asked to change it to perl, as we had to move to windows based machine. The perl script ran fine from the command prompt but when I tried this from the scheduler, the job failed with "ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory" error. No extra messages were found in all_scheduler_job_run_details. While debugging I realized if I commented out DB related commands the job ran fine. I tried including the path to libraries (see entries in green), yet it fails. Probably missing some more libraries. I have attached apart of my perl script below. Please advise.
Appreciate any help or suggestions.
Thanks in advance
#!/usr/bin/perl
use DBI;
use strict;
$ENV{'ORACLE_HOME'} = '/product/app/11.2.0/db_1';
$ENV{'ORACLE_SID'} = 'XXX';
$ENV{'LIB_PATH'} = '$ORACLE_HOME/lib';
$ENV{'PATH'} = '$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/opt/perl5_64/lib/site_perl/5.18.2/aix-thread-multi-64all:.$LIB_PATH
$ENV{'LD_LIBRARY_PATH'} = '$GG_HOME:$LIB_PATH:/usr/opt/perl5_64/lib/site_perl/5.18.2/aix-thread-multi-64all';
#my ( $inst, $user, $pass ) = @ARGV;
my $inst = "XXX";
my $user = "aaa";
my $pass = "aaa";
# So we don't have to check every DBI call we set RaiseError.
# See the DBI docs if you're not familiar with RaiseError.
# AutoCommit is currently encouraged and may be required later.
print($inst, $user, $pass, "\n" );
my $dbh = DBI->connect( "dbi:Oracle:$inst", $user, $pass,
{ AutoCommit => 0, RaiseError => 1, PrintError => 0 } )
or die "Unable to connect: $DBI::errstr";
exit 0;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DBMS_SCHEDULER and perl script [message #625889 is a reply to message #625858] |
Wed, 15 October 2014 09:00  |
John Watson
Messages: 8979
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
rrk1 wrote on Wed, 15 October 2014 08:24 <snip>
beside running the pl/sql procedure it has to run some Oracle Golden Gate commands to perform initial loading of data. I donot know how to include the GG commands in pl/sql. Hence I have to run a Perl script which will call both the pl/sql procedure and the Oracle GG initial Load script. Well, I would do it as a job chain: one jobs to run the pl/sql then another to run the GG stuff. To invoke the GG code, just use an external job that invokes your shell interpreter with the name of the script as an argument.
--update: quoted the wring message.
[Updated on: Wed, 15 October 2014 09:02] Report message to a moderator |
|
|
|
|
|
| Re: DBMS_SCHEDULER and perl script [message #625900 is a reply to message #625898] |
Wed, 15 October 2014 15:24 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I am going to guess what BlackSwan says is happening here. I believe (but I am usally wrong when guessing) that the call to the script has no idea of the environment at all. I think you are going to have to set all those environment variables in the script itself.
You can test this by echoing them out to a file and see the results.
|
|
|
|
| Re: DBMS_SCHEDULER and perl script [message #625901 is a reply to message #625900] |
Wed, 15 October 2014 15:28 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>You can test this by echoing them out to a file and see the results.
I use below for testing batch scripts
#!/bin/bash
/usr/bin/env > /tmp/capture.env
# rest of actual script starts below here
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
