Re: How to get the returncode from a SQL-statement in a stored procedure

In article <01bbe9e3$2c6b5420$0100007f_at_pc-jsultan>,

   "J. Sultan" <jsultan_at_tecs.de> wrote:
>How can I obtain the returncode/result of an SQL-Statement in a stored
>procedure?

if you want to know how many records were inserted, updated, or deleted, you can return the value of SQL%ROWCOUNT. Example:

	update fnortz set xyzzy=1 where plugh='X';
	return sql%rowcount;

if you want to return an error code from a sql that failed and aborted pl/sql, wrap the SQL in a BEGIN/EXCEPTION/END block, and if the OTHERS exception fires, it can save the SQLCODE value. Example:

	BEGIN
	 insert into something (select this from that);
	EXCEPTION when OTHERS then
	 return SQLCODE;
	END;
	return 0; /* no error */		

--
Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.