OraFAQ Forum: SQL & PL/SQL » Last returned SQLCODE?!?!???

Home » SQL & PL/SQL » SQL & PL/SQL » Last returned SQLCODE?!?!???

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Last returned SQLCODE?!?!??? [message #21275]

Tue, 23 July 2002 17:35

Su
Messages: 154
Registered: April 2002

Senior Member

Hi there,

Does any of you have any idea to retrieve the last processed/returned SQLCODE in interactive mode (ie. at SQLPLUS)? Is there any function or built-in variable that represents last returned sql error/status code, just like $? in unix. Here the problem is, I have to execute a series of SQL statements (just like a script, but not pl/sql), there I want to write the SQL status/error/return code to a log file every time ORACLE executes an SQL command. If it is in embedded sql like PRO*C, I could get it from SQLCA.SQLERR(), but same I want to achieve it in an interactive or script SQL. Any idea? Any suggestion/advice to resolve this, would be greatly appreciated.

Thanks in advance.

Report message to a moderator

Re: Last returned SQLCODE?!?!??? [message #21279 is a reply to message #21275]

Tue, 23 July 2002 19:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

If you exit from SQL*Plus using EXIT SQL.SQLCODE after
performing a correct SQL statement, SQL*Plus will
return a 0 to this O/S environment variable. If an incorrect SQL statement is generated, then a code
other than 0 will be returned.
anyhow, you can do something like this.
create a .sql script file which has all your other scripts.
from the prompt you can directly run the that sql file.
(make sure u exit of the sql*plus at the end)
something like

sqlplus connect scott/tiger@test1 @myscript.sql > Log1.log

now log1.log will account for all the successful and
unsucessful sql statements.

Report message to a moderator

Re: Last returned SQLCODE?!?!??? [message #21335 is a reply to message #21279]

Fri, 26 July 2002 08:16

Su
Messages: 154
Registered: April 2002

Senior Member

Thanks for the reply. But what my requirment was to see SQLCODE not after exiting Oracle. If I write a .sql file containg a number of SQL statements and want to see the sql returncode returned every time after it executed a single SQL statement, at the end of transcation process. In a (por*c) pl/sql block, I could use a temporary array to store the sqlca.sqlerr(2) and look at the array contents at last to find out, was there any error and if so, what, by checking the error numbers stored in it. The same I want to achieve it in a .sql script.
First of all, how can I see the last returned SQLCODE (or something else) at SQL prompt without exiting ORACLE.

Any help to resolve this, would be highly appreciated.

Thanks.

Report message to a moderator

Re: Last returned SQLCODE?!?!??? [message #21337 is a reply to message #21335]

Fri, 26 July 2002 09:16

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL*Plus does not have a DEBUG capability.
I dont think, there is anyway to do this, except spooling.

Report message to a moderator

Previous Topic:	query??
Next Topic:	Returned vArray problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 09:32:07 CDT 2024