CODEWITHSUNDEEP
oracle-databaseplsqlexception
Arpan Paliwal
Arpan Paliwal

Reputation: 264

What will be the ORA code if the Stored Procedure executes nothing?

I have a Stored Procedures which I am calling from Java code. There is an IF block and that IF condition is false the procedure executes nothing. So what will be the ORA code in this case?

Upvotes: 1

Views: 585

Answers (2)

APC
APC

Reputation: 146239

The ORA code will be ORA-00000: normal successful completion because no errors occurred.

If you want a different outcome you need to code something specific. What you do depends on the business rules you're enforcing. Perhaps you need to raise an exception? This example tests whether a parameter is populated and hurls an exception if it isn't:

if p_str is null then
    raise_application_error(-20000, 'parameter P_STR must be populated');
else
    ....
end if;

In this scenario the ORA code will be ORA-20000. Oracle reserves error numbers -20999 to -20000 for our own use.

"what if update is running with no change in table"

Same thing. Anything which does not hurl an exception is a successful completion. In this case we can test whether an update changed anything with the sql%rowcount value:

update your_table
set whatever = p_str
where id = p_id; 
if sql%rowcount = 0 then
     raise_application_error(-20001, 'No rows in YOUR_TABLE match ID = '||p_id);
end if;

Upvotes: 4

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

The value returned by a call to SQLCODE (the "ORA code" you mention) is always going to be zero unless the function is called from within an exception handler.

Upvotes: 0

Related Questions