in reply to sqlplus or DBI

Typically when calling sqlplus scripts from any outside program... be it Perl or Shell... I wrap the sql commands in the following: 
whenever SQLERROR exit SQL.SQLCODE
set feeback off
set verify off
set lines 32
set pages 0
set linesize 500
set tab off *

your script

exit SQL.SQLCODE

*SQLPlus often formats white space in output using tab characters, not
+ spaces... "set tab off" prevents this

[download]
You probably also want to use the -s switch to use silent mode. After implementing this, you can say: 
$result=`sqlplus -s user/pass \@myfile`;
if($result=~/(?:SP2|ORA)/){
  print "SQL ERROR: $result\n";
}

[download]

Replies are listed 'Best First'.
Re: Re: sqlplus or DBI
by tommyw (Hermit) on Nov 19, 2002 at 12:01 UTC

    Ooh, no! You don't want to do that!

    The system status code is only one byte wide, and so can only handle values in the range 0 to 255. Picking a random example, do you know what error code 244 refers to? Yes, that's right: "integrity constraint (%s.%s) violated - child record found"

    The point here is that the particular error I've quoted above is actually code 2292, which any good mod function can tell you is 256*8+244. The multiples of 256 get thrown away by the operating system.

    Now, what do you think happens if you get the error "space quota exceeded for tablespace '%s'"? Yes, that's right: it's code 1536, which is exactly 256*6. And the system neatly truncates this to 0.

    Actually, the best solution I found was to use WHENEVER ERROR EXIT 2, and terminate the script with EXIT 1. That way, you can tell if the script never started (exit code 0), completed successfully (code 1), or failed (code 2).

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
      Remember that using backticks to execute a call is not the same as using system(). The latter returns the exit status...the former the output of the call. So when I call a sql script that will exit with sql.sqlerror and it fails with ORA-2292... the errorcode will indeed be the value in $result as long as it is called with backticks or qx//.
      Jason
[reply]
[d/l]
        Even when using backticks you can retrive the last exit status using $? or $CHILD_ERROR.
[reply]

        Well, actually, I have to apologise: I didn't read your post to the bottom and didn't notice that you were parsing the output rather than looking at the status code.

        But my point was actually about exit SQL.SQLCODE, because it's at best useless (as you intend it to be), or at least confusing, and at worst dangerous (for the reasons I gave above).

        --
        Tommy
        Too stupid to live.
        Too stubborn to die.

[reply]
[d/l]

In Section Seekers of Perl Wisdom