Coding Day

I use for TOAD the development as the oracle IDE like most people. When I’ve heard about oracle sql I was wondered, first of all it is developed by the oracle itself, second it’s free :)The first version of the product was bad, I was unable yo use it properly, but with the patch 2 it is much better. I suffer TOAD a lot while writing pl-sql. I am not feeling very comfortable without intellisense, and Oracle Sql Developer does that great.

Screenshots

Connection Settings Window, very simple to configure.

Editing a stored procedure

Intellisense on table names, table columns, procedure names, packet names

I was having trouble with one of my SPs and I needed to handle it correctly. As a result I needed to "throw" (raise) the exception. In that case we have three oppurtunities. SQLCODE and SQLERRM can be used to output the message.

• Throw one of the default exceptions

These exceptions are defined by the database itself. Like ZERO_DIVIDE NO_DATA_FOUND exceptions. OTHERS will catch everything other that you have specified. To have the behaviour of try catch statements, it’s a good practice to write your exception handler in begin and blocks.

BEGIN
--DO SOMETHING
EXCEPTION WHEN ZERO_DIVIDE THEN
--UPDATE SOMETHING
WHEN OTHERS THEN
P_CODE:= SQLCODE;
P_MSG := SQLERRM;
END;

• Throw user defined exceptions

We are not limited to oracle’s internal exception types we can declare our exception variable and throw (raise) it.

userisnotonline EXCEPTION;
BEGIN
--DO SOMETHING
IF x>5 THEN
RAISE userisnotonline;
END IF;
COMMIT;
WHEN userisnotonline THEN
ROLLBACK;
P_message := 'user is not online';
RAISE userisnotonline;
END;

• Raise an application error

If you want to provide a customized output for your error messages or want to make something more complex than oracle’s exceptions you can write your own application errors. User defined exception can not contain any  information, you can not specify the output or code of the error. raise_application_error command can be utilized for sending your custom error messages.
The first parameter is the error number. We have -20000 to -20999 numbers that we can use.
We don’t have rights to debug and trace in our development Oracle database. So I use raise application error for tracing, just like alert in javascript. It is not the correct way, but it works.

-- insert something, get the primary key to a variable.
IF p_key >3 THEN
raise_application_error(-20001, 'This field cant be lower than 3');
END IF;