Re: How to get the returncode from a SQL-statement in a stored procedure
Date: 1996/12/18
Message-ID: <32b75c59.6394364_at_dcsun4>#1/1
On 14 Dec 96 17:30:59 GMT, "J. Sultan" <jsultan_at_tecs.de> wrote:
>I'm working on a project with an Oracle 7 DB at my university and have the
>following problem:
>How can I obtain the returncode/result of an SQL-Statement in a stored
>procedure?
>There must be a sysvar or something that tells me if my Insert, Update,
>etc. has succeeded.
>
>Please give me hint. time is running up and the Oracle books online don's
>say much about it.
>
>Jan
>
>ps: if possible, please send a cc to my mail adress, thanks
>
insert/update/delete statements executed in pl/sql blocks will throw execeptions. You can inspect the sqlcode and or sqlerrm functions to see what the error was. For example:
....
begin
insert into t values ( x );
update t set y = 5;
delete from t where a = b;
exception
when dup_val_on_index then ....
when ....
when others
if ( sqlcode = 321 ) then
....
end if;
raise;
end;
If any of the insert/update/delete fails, an exception will be raised.
So, an insert/update/delete will always throw an exception when there is an error (you don't need to have error code after each and every one) and you can look at the sqlcode builtin to see what the error was...
If you need to verify you updated/deleted/inserted something, you can inspect the SQL%ROWCOUNT builtin after each statement to see how many rows it affected.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Dec 18 1996 - 00:00:00 CET