A reader, September 04, 2002 - 11:01 pm UTC
Tom,
We regularly use "grep ORA- > err.log" on the log files to trap the error messages. Do you know how we can trap the preceding 2 lines of the line that has ORA- error message also in the error log.
Eg:
line1
line2
line3
line4 ORA-xxxx
line5
I want to trap
line2
line3
line4 ORA-xxxx
in the error log. Is it possible? Thanks.
September 05, 2002 - 7:30 am UTC
write an awk script would be one way.
Oracle error to Unix
Philip, February 09, 2004 - 10:06 am UTC
I am trying to call a procedure from a unix script through sqlplus and I would like to stop the processing of unix script if any errors occur in the procedure. I am not able to pass the error code from my procedure to Unix. I use WHENEVER SQLERROR EXIT SQL.SQLCODE before executing the procedure and tried to check the status(echo $status), but it shows null.
Could you help me to find out what's wrong?
February 09, 2004 - 10:22 am UTC
might you be using sh or some other scripting environment? (i used csh, it'll vary)
for example:
[tkyte@tkyte-pc tkyte]$ cat t
#!/bin/sh
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
raise program_error;
end;
/
exit
EOF<b>
echo 'Status = ' $?</b>
[tkyte@tkyte-pc tkyte]$ sh ./t
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 9 10:25:57 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ops$tkyte@ORA920PC> ops$tkyte@ORA920PC> 2 3 4 begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 2
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production<b>
Status = 101
</b>[tkyte@tkyte-pc tkyte]$
would be the sh way of doing it. refer to your "man" page for your shell.
Also -- remember, exit codes are in the range 0..128 or 255 -- best to use
whenever sqlerror exit 1
(as noted above) instead of the sqlcode. run this script to see why :)
#!/bin/sh
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20224, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
sqlplus / <<EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20225, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
very useful
Andrew, February 09, 2004 - 1:57 pm UTC
philip, February 10, 2004 - 10:43 am UTC
It seems very useful and a working solution, but some how it is not working for me. I am using sh(Bourne shell) and the only difference I can see is I am calling a database procedure through sqlplus instead of commands direct.
I have WHENEVER SQLERROR EXIT 1 before I call the procedure, but when I do echo $? after sqlplus I always get a value of 0 even if there is an error in the procedure.
February 10, 2004 - 3:32 pm UTC
how about "man sh" on your system to see what it says would be "right" for you.
or how about "you post your example" like I did mine and we can take a look see..
or how about you run my example "as is" to see if in fact it seems to work....
philip, February 11, 2004 - 1:49 pm UTC
I tried your example but it also give me a value 0 for the status. It seems nothing gets executed between the begin and end. I tried to run the script with some mispelled commands, it didn't give me an error. I tried to execute some valid SELECT statements, but I didn't see any results.
I think it just connect to Oracle and disconnect without executing any commands(or at lease I don't see any rsponse from Oracle). But the interesting thing is I am able to execute a database procedure from the script. No idea what's wrong and where?
February 11, 2004 - 2:47 pm UTC
your shell must be a totally "non standard shell" then.
this is really "basic" stuff -- works on solaris, works on linux, should work on pretty much any "standard" unix like OS.
If it is not working -- remove sqlplus from the equation all together and show us something that does work on your system. The problem is *not* sqlplus here at all -- but rather that we don't know how to write "sh" on your system (whatever it is)
philip, February 11, 2004 - 3:42 pm UTC
My Unix is HP-UX miladms B.11.11 U 9000/800 3255374999. The default shell is called POSIX which is similar to Korn shell and I don't have any problem in executing any unix script on this shell.
As I said in my previous response I am even able to run Oracle procedures by calling sqlplus from the shell script. So there is nothing wrong with the standard execution capability of the shell. The problem is sqlplus not returning the correct exit codes to Unix. It always return 0 no matter you have error in your procedure or not. It seems I need to setup something in the sqlplus environment to return the status properly? Following is the snippet of the script.
sqlplus -s test_721_d/tecsys@fnok_dms >>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec gl_export_hyperion('$EXPFILE','$EXPDIR')
set serveroutput off
set feed on veri on head on
exit
EOF
echo Status $?
February 11, 2004 - 5:18 pm UTC
do this,
compile this C program:
void main()
{
exit(55);
}
call it test.c and just
$ make test
then:
#!/bin/sh
./test
echo status = $?
and show us what that does. remove sqlplus from the equation here.
Watch your indentation
Alfonso, February 11, 2004 - 5:44 pm UTC
Indentation matters in Philip's code section.
This sample returns "Status = 255" (using ksh)
sqlplus / <<-EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
But this sample returns "Status = 0" (note indented lines):
sqlplus / <<-EOF
whenever sqlerror exit sql.sqlcode
begin
raise_application_error( -20223, 'foo' );
end;
/
exit
EOF
echo 'Status = ' $?
Try Tom's examples verbatim and you shouldn't have a problem.
Regards.
February 11, 2004 - 6:54 pm UTC
the "/" not being in position 1 would be the cause.
there is no code being run.
Philip, February 12, 2004 - 9:08 am UTC
First let me answer to Tom.
I ran your C program and it gave me a status of 55 which I think what you are expecting.
To Alfonso
I ran both of your samples and both gave me a status of 255 and I don't think indentation matters. When you call a procedure from the script you don't need to use / at the end, Right? Please look at the code I listed in my previous response and let me know if anything wrong with that.
February 12, 2004 - 9:14 am UTC
I don't have a shelled called "posix", but in ksh, that EOF needs to be like this:
[tkyte@localhost tkyte]$ cat ./test.sh
#!/bin/ksh
export LOGFILE=foo.lst
sqlplus -s />>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec raise program_error;
set serveroutput off
set feed on veri on head on
exit
EOF
echo Status $?
[tkyte@localhost tkyte]$ ./test.sh
Status 1
[tkyte@localhost tkyte]$
"works for me"
don't know what else to say except try the SH or KSH -- using code provided above, as is -- verbaitim.
indentation does matter -- alot.
Unsolicited $ 0.02
Madhu, February 12, 2004 - 9:34 am UTC
Here is another approach we use to pass the exact Oracle error code from SQLPLUS to unix . We use HP-UX/ Korn Shell
RCODE=`sqlplus -s / << EOF
SET SHOWMODE off
SET ECHO off
SET FEEDBACK off
SET VERIFY off
SET RECSEP off
SET PAGES 0
SET TRIMSPOOL on
SET LINESIZE 2000
VARIABLE RCODE NUMBER ;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
DECLARE
BEGIN
:RCODE := rso_gen_email_info('${EMAIL_FILE}');
END;
/
print :RCODE
exit :RCODE
EOF`
echo $RCODE
This will print the exact Oracle return code . As Tom mentioned in previous post, indentation matters a lot .
Thanks,
Madhu
Philip, February 12, 2004 - 10:34 am UTC
Sorry guys, I wasn't capturing the error in my procedure correctly. I was under the impression that oracle will return the error even if I didn't raise_application_error for oracle defined exceptions. I was just using dbms_ouput to capture the error.
There was nothing wrong with the shell or the useage of the script, all because of the procedure. I think you will excuse since I am still learning pl-sql programming.
Thanks any way for the great help you all guys provided in this regard.
Significance of Indentation for UNIX Inline Input Redirection
Dave Kramer, Oracle APPS DBA, SBC Services, Inc, May 22, 2004 - 10:12 am UTC
The content of the inline input can be safely indented using spaces or tabs provided:
1) a hyphen is used to prefix the end of file string
2) the end of file string appears anywhere on a line by itself -- it can be indented using TAB characters.
Building on the prev example in the thread:
$ cat -vt demo4.sh
#!/bin/ksh
export LOGFILE=foo.lst
sqlplus -s />>$LOGFILE 2>&1 <<-EOF
set feed off veri off head off
set serveroutput on
whenever sqlerror exit 1
exec raise program_error;
set serveroutput off
set feed on veri on head on
exit
^IEOF
echo Status $?
$ ksh demo3
Status 1
$ cat foo.lst
BEGIN raise program_error; END;
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 1
$
Alternative tests:
1) remove the tab --> success;
2) replace the tab with 1 to many spaces --> fails to execute sqlplus.
Dealing with UNIX far more than SQL*Plus, I have always used tabs for all indentation.
how that Unix RC is determined?
david s, January 03, 2005 - 12:35 pm UTC
SQL> select 'a'+1 from dual
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and OLAP options
JServer Release 9.2.0.5.0 - Production
/home>echo $?
186
Might it be true that the Oracle code "1722" can be modded with 256 to generate 186? This worked also with 20223 code going to 255. Only 2 examples, but does show promise...
January 03, 2005 - 10:22 pm UTC
yes, but don't forget -- 1 out of every 256 error codes mod to 186 as well!!!
I'd stick to a 'return 1' for an error
Gary, January 03, 2005 - 11:19 pm UTC
"1 out of every 256 error codes mod to 186 as well!!! "
and, 1 out of every 256 will mod to zero too. If you use a return code of zero to indicate success, you may miss errors including :
ORA-01536: space quota exceeded for tablespace ....
ORA-12800: system appears too busy for parallel query execution