2 replies to this topic

[olvitar]

Hi there, I am trying to find out the differences and similarities on how exceptions are handled in Java and PL/SQL. I did some research and found out what exceptions are, but i couldn't find any good examples or explanations of how they are handled. They both seem the same to me.

[Auxcil]

IDK about SQL but I know in Java you can set how to handle them.

[wim DC]

When we learned PL/SQL in sqldeveloper. we would type

EXCEPTIONS

  WHEN exception_name THEN

     dbms_output.putline('Something went wrong');

     //you can do other stuff like updates here

This comes as final part in the procedure/function, so right before the END keyword.

so in java it's

catch (Exception_name) {

in plsql it's

when exception_name then

When in Java you want to catch any exception, as exception type you simply give 'Exception'
In plsql this becomes
When others then


In Java you need to put a try {} block around the code that you want to catch the errors from. In pl/sql you don't.

Note that if your program fails and raises an error without catching it, the database will automatically rollback and not commit. If you catch it however it will be successful and not rollbacked. So when others should be used wisely.

list of exception names:

Quote

ACCESS_INTO_NULL
CASE_NOT_FOUND
COLLECTION_IS_NULL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGIN_DENIED
NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR
ROWTYPE_MISMATCH
SELF_IS_NULL
STORAGE_ERROR
SUBSCRIPT_BEYOND_COUNT
SUBSCRIPT_OUTSIDE_LIMIT
SYS_INVALID_ROWID
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE