Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Passing error from PL?SQL to UNIX script wrapper. (How?)
Thomas Kyte wrote:
> Are you using 'whenever sqlerror exit sql.sqlcode'??
>
> For example, on my solaris machine the following csh script:
>
> #!/bin/csh -f
>
> cat > test.sql <<"EOF"
> whenever sqlerror exit sql.sqlcode
(snip)
> "EOF"
>
(snip)
> ORA-01403: no data found
>
> Disconnected from Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
> With the Partitioning and Objects options
> PL/SQL Release 8.0.3.0.0 - Production
> The exit code is: 123
>
> So, it sees 123 as the exit status...
The problem with this is that the exit status is not unique to the SQL error. The SQL error (1403) has been modded by 256, giving 123. This is *NOT* the problem of Oracle. Most Unices have a 1 byte process return status.
Thus 123 might have been generated by Oracle errors (using 7.3 on my LINUX box)
00123, 00000, "idle public server terminating" 01147, 00000, "SYSTEM tablespace file %s is offline" 01403, 00000, "no data found" 01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s" 01915, 00000, "invalid auditing option for views" 02171, 00000, "invalid value for MAXLOGHISTORY" 02427, 00000, "create view failed" 06011, 00000, "NETASY: dialogue too long" 06267, 00000, "NETNTT: bad state" 06779, 00000, "TLI Driver: error reading ccode" 07547, 00000, "sfcmf: $OPEN failure" 07803, 00000, "slpdtb: invalid packed decimal nibble" 08315, 00000, "sllfrb: Error reading file" 09851, 00000, "soacon: Archmon unable to lock named pipe." 10107, 00000, "CBO Always use bitmap index" 12155, 00000, "TNS:received bad datatype in NSWMARKER packet" 12411, 00000, "DBHIGH must dominate DBLOW"etc
The following perl script (called test.pl) could generate the previous output if invoked as "./test.pl 123 100"
#!/usr/bin/perl
$OracleReturn = $ARGV[0] ; # The return code is the first argument $MaxTimes = $ARGV[1] ; # How many loops $MaxTimes = 100 if ($MaxTimes eq '') ; foreach $a (0..$MaxTimes) { $b = $a * 256 + $OracleReturn ; $cmd = "oerr ora $b | head -1" ; system $cmd ;
Hmmm. Guess the unix wrapper script needs to be smart enough to disambiguate potential causes of the problem....Not likely.
On the other hand, have you thought of coming the other way around and using something like perl (with the DBI/DBD::Oracle interface and the classic Oraperl syntax) to run the PL/SQL block? It maintains the oracle error number and string automagically.
More serious is where the mod of the Oracle error with 256 will yield a zero, which UNIX will treat as OK.
00000, 00000, "normal, successful completion" 00256, 00000, "error occurred in translating archive text string '%s'" 07680, 00000, "sou2os: another call to ORACLE currently executing" 01024, 00000, "invalid datatype in OCI call" 12800, 00000, "system appears too busy for parallel query execution" 01536, 00000, "space quota exceeded for tablespace '%s'" 01792, 00000, "maximum number of columns in a table or view is 254" 02048, 00000, "attempt to begin distributed transaction without loggingon"
06144, 00000, "NETTCP: SID (database) is unavailable" 06400, 00000, "NETCMN: No default host string specified" 06912, 00000, "CMX: write error in datarq" 07424, 00000, "sfwfb: write failed because buffer was not alignedproperly."
07680, 00000, "sou2os: another call to ORACLE currently executing" 08448, 00000, "syntax error in DECIMAL-POINT environment clause" 09984, 00000, "SGA file $ORACLE_HOME/dbs/sgadef$ORACLE_SID.dbf does notexist"
10240, 00000, "dump dba's of blocks that we wait for" 12544, 00000, "TNS:contexts have different wait/test functions" 12800, 00000, "system appears too busy for parallel query execution"
Perhaps when running on 64 bit machines, this truncing problem might be different.
A solution would be allowing writing (%d) the error code when abending from SQL*Plus, svrmgrl, etc, preferably to stderr. Maybe this needs an extra flag or settable "whenever". You could redirect stderr to something that looks at the last line and converts it to an integer. Response expected? Notime soon.
Summary: run your PL/SQL blocks from within perl. See http://reference.perl.com/query.cgi?database for details
--
David T. Bath (from home) david.bath_at_nre.vic.gov.au
Phone: +613 9500 0894 Mobile: 015 824 171 (not always on)
Office: Global Consulting Ph:+613 9347 7511 Fax:+613 9347 0182
#include <std_disclaim.h>
Received on Wed Jun 24 1998 - 01:22:38 CDT