CODEWITHSUNDEEP
c#oracle-databasestored-proceduresplsql
l3gion
l3gion

Reputation: 73

PL/SQL Exceptions and Errors Handling

I'm doing a project in Asp .Net and C#. I'm also using ODP .NET to connect to my Oracle DB.

I am using a stored procedure to insert a value into the database. Everything is fine, it also raises all the exceptions I have.

I have 3 exceptions:

  1. when the value I try to insert already exists in the database
  2. when i try to insert a null value
  3. Default "Others"

When the first two exceptions are raised I insert the error into a table Errors, everything goes well.

What I really want to know is, how can I show a message to the user when a Exception occurs?

Something like dbms_output.put_line("Error");...but I want it to show in my webpage. Is this possible?

Any tips are welcome, thanks in advance.

Upvotes: 1

Views: 2244

Answers (3)

ObiWanKenobi
ObiWanKenobi

Reputation: 14892

Since your .NET program is your client, you should let any unhandled exceptions propagate from your PL/SQL program and back to the client, where you can deal with it like any other exception.

In other words, you should remove the "when others" exception from your PL/SQL code, and instead wrap your database call (using ODP.NET) with a C# exception block. There you can catch the exception and get the Oracle error number and text, and display it to the user if you want.

(Using this approach you can also use RAISE_APPLICATION_ERROR in your PL/SQL code to signal errors back to the C# client.)

Upvotes: 4

Davide Piras
Davide Piras

Reputation: 44605

In our sql server stored procedures we have something like this:

IF @@ERROR <> 0

return (1)

ELSE

return (0)

but it depends on what the stored does and what else it returns, if the stored returns a table, you can then use output parameter to send back the integer discussed above.

Upvotes: 0

Davide Piras
Davide Piras

Reputation: 44605

You can fix the stored procedure to return an integer, 0 means ok, 1 means exception of type 1, 2 exception of type 2 and so on... in this way the UI can notify the user that saving method did not go well as expected.

Normally I would not have handled the exception in the stored procedure but I understand that you want to log the error from SQL so the above way should allow you to do what you want.

Upvotes: 1

Related Questions