chuckster has asked for the wisdom of the Perl Monks concerning the following question:

I'm having a tough time getting a failure code back into my PERL program when invoking SQL Plus via the system command. I have run_package.sql 
begin
  forbesc.test_exception_pg.main('orafail');
end;
/
exit

[download]
and invoke_sqlplus.pl 
#!/usr/bin/perl -w
my $sqlplus = "sqlplus -s chuckster/im_in\@develo";
my $file = "\@/home/prodadm/scripts/run_package.sql";
my $var = (system($sqlplus . " " . $file));
my $out = $? >> 8;
print "The out value is '$out'.\n";

[download]
I'm pretty sure that the reason I'm always getting a 0 for $out is because the 'exit' in run_program.sql is executing successfully, but I want to know when the Oracle package is not executed successfully. I know it's not in the above case, as the output looks like: 
[prodadm@ora4 scripts]$ ./invoke_sqlplus.pl 
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "FORBESC.TEST_EXCEPTION_PG", line 12
ORA-06512: at line 2


The out value is '0'.

[download]
I'm at a loss. Could someone help plant a fire under me and get me going in the right direction? Thanks, Chuckster

Replies are listed 'Best First'.
Re: invoking Oracle's SQL Plus, return code always indicates success
by Roy Johnson (Monsignor) on Apr 06, 2005 at 22:58 UTC
    If you need to use SQL*Plus (instead of DBI), you'll want a WHENEVER SQLERROR statement in the file.
    Caution: Contents may have been coded under pressure.
[reply]
      Thanks. That sounds like it'll work. We're avoiding DBI at this time, as we just need simple scripts which our Scheduling Tool can execute. Am I allowed to give you points towards PerlMonkdom, or can only others do that? I can't figure out how yet, but I will if you give me the URL which describes the process.
[reply]
        Unless you absolutely need to, use a shell script for this task instead of Perl. Or, spend the time to build an infrastructure in Perl that will stand you in good stead. I worked on scheduling tools (Control-M primarily, though some Tivoli and SQL*Server Scheduler) for 6 months on a contract and that's what we ended up doing. Devoted a month to it and did a year's worth of work every month after that. That's not counting the work we didn't even think was possible that was now a piece of cake.

        My wife's blog
[reply]
        You gain the ability to upvote and downvote posts after you yourself have gained some experience points. See Levels of Monks for what awaits you if you devote yourself to the monastery.
        Caution: Contents may have been coded under pressure.
[reply]
Re: invoking Oracle's SQL Plus, return code always indicates success
by Joost (Canon) on Apr 06, 2005 at 22:56 UTC
[reply]

Back to Seekers of Perl Wisdom