Home » SQL & PL/SQL » SQL & PL/SQL » Return code from a SQL script
Return code from a SQL script [message #233074] Tue, 24 April 2007 09:30 Go to next message
german83
Messages: 4
Registered: April 2007
Junior Member
Hi everybody,

Im working with an aix over one year and i have developed a PRO*C multithread process that reads commands from a table and execute it in parallel (one command by thread) so depending the process exit code, I put a mark (OK or ERROR) in that table.

My problem: there is ONE process (a shell calling sqlplus that execute a store procedure by a sql script) that terminate Ok and its returns an exit code different from 0 and consequently I put a mark erroneous.

My question: Is there any way that plsql finishes ok (no exceptions occurs) and sqlplus return a non zero exit code?
Perhaps the problem is not in the procedure but in the call (sql script)

Sorry my english, I really hope you can help me.
Thanks in advanced.
German.
Re: Return code from a SQL script [message #233082 is a reply to message #233074] Tue, 24 April 2007 10:05 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I never used it but maybe you can use :
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/ch6.htm#1006738

Quote:

Exiting from a Script with a Return Code

If your script generates a SQL error while running from a batch file on the host operating system, you may want to abort the script and exit with a return code. Use the SQL*Plus command WHENEVER SQLERROR to do this; see the WHENEVER SQLERROR command for more information.

Similarly, the WHENEVER OSERROR command may be used to exit if an operating system error occurs. See the WHENEVER OSERROR command for more information.

Re: Return code from a SQL script [message #233087 is a reply to message #233074] Tue, 24 April 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any way that plsql finishes ok (no exceptions occurs) and sqlplus return a non zero exit code?

No, no default way.
Do you want it to occur? Then you can do it.

Regards
Michel

[Updated on: Tue, 24 April 2007 13:45]

Report message to a moderator

Re: Return code from a SQL script [message #233102 is a reply to message #233074] Tue, 24 April 2007 13:18 Go to previous messageGo to next message
german83
Messages: 4
Registered: April 2007
Junior Member
Yes, i use it.
I think that the '/' is wrong and is generating some kind of problem and then the script finishes bad.
Executing the script with sqlplus from the shell (ksh) I see a zero return code, but not when it is executed from my process.
Perhaps its a OS error but I want to discard the possibility that is the script.

The script look like this:

&1 is a param for the procedure
&2 is the connection string
$DIRLOG is a ksh enviroment variable

spool $DIRLOG//log_file
whenever oserror exit oscode
whenever sqlerror exit sql.sqlcode
connect &2
/
exec sql_procedure('&1')
/
show errors
spool off
exit
/

Greetings.
German.

[Updated on: Tue, 24 April 2007 13:18]

Report message to a moderator

Re: Return code from a SQL script [message #233104 is a reply to message #233102] Tue, 24 April 2007 13:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Change the end of your script to this:

exec sql_procedure('&1')
/
--show errors
spool off
exit sql.sqlcode
--/


Be sure to test your script with invalid user/pass, wrong name or path for sqlplus etc. to make sure all errors are trapped. Also, rather than using "/", you may like to just use ";". The problem with / is that is runs whatever is in the buffer at that time.
Re: Return code from a SQL script [message #233106 is a reply to message #233102] Tue, 24 April 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use:

var exitcode number
exec :exitcode := sql_function('&1')
exit :exitcode

Regards
Michel
Re: Return code from a SQL script [message #233121 is a reply to message #233074] Tue, 24 April 2007 16:18 Go to previous messageGo to next message
german83
Messages: 4
Registered: April 2007
Junior Member
Thanks guys, both solutions worked ok.
The problem was in script.
I suppose that the problem was the sentence exit without value of return.

Thanks a lot again.
German.
Re: Return code from a SQL script [message #233123 is a reply to message #233121] Tue, 24 April 2007 16:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
No, you can definitely just use "exit".

Sample script...
#!/usr/bin/ksh
## create or replace procedure my_proc (p_rows in number) is
## begin
##    for i in (select object_id * rownum * rownum big_num, object_name, created
##                from user_objects where rownum <= p_rows) loop
##       dbms_output.put_line 
##           (lpad (i.big_num, 15, ' ')|| ' ' || rpad (i.object_name, 15, ' ')|| i.created);
##    end loop;
## end;
## /

user=xyz
pass=abc
db=dev1

num_rows=5

sqlplus -s /nolog <<EOF >tmp.txt
 connect $user/$pass@$db
  whenever sqlerror exit sql.sqlcode rollback
  set feedback off
  set serveroutput on size 1000000 format wrapped 
    exec my_proc($num_rows);
  exit sql.sqlcode;
EOF
rv=$?

echo "---- start output ----"
grep -v "Connected." tmp.txt
echo "---- end output ----"

echo Retval is $rv

[Updated on: Tue, 24 April 2007 17:10]

Report message to a moderator

Re: Return code from a SQL script [message #233127 is a reply to message #233074] Tue, 24 April 2007 16:59 Go to previous messageGo to next message
german83
Messages: 4
Registered: April 2007
Junior Member
So u say that using just "exit" the script will always return the right exit code?
I supposed that because I saw that it was the only difference but I tried this way and also it worked well (Discarding some things from the original code)

whenever sqlerror exit failure
set termout on
set serveroutput on size 100000
exec sql_procedure('&1')
exit
/

Do you see something strange comparing it with the original code?
Re: Return code from a SQL script [message #233128 is a reply to message #233127] Tue, 24 April 2007 17:12 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
No, I think "exit" is same as "exit 0". I can't confirm right now though...
Previous Topic: Table Designing
Next Topic: how can make use of p_tier_id and still compiled?
Goto Forum:
  


Current Time: Sat Dec 03 20:06:07 CST 2016

Total time taken to generate the page: 0.05027 seconds