PL/SQL: How to use EXCEPTION statement before INSERT?
Hi Experts
how to use EXCEPTION statement before INSERT stm in PL/SQL Block
Please help me with syntax or format is this option possilbe as well?
Software/Hardware used:
Software/Hardware used:
ASKED:
December 12, 2008 4:20 AM
UPDATED:
January 5, 2012 10:32 AM
Answer Wiki:
Could you please give more details about your question ?
If you put an EXCEPTION handler BEFORE the insert statement, that exception handler will not catch any error raised when the insert is executed. Exception handlers catch errors raised by instructions between the BEGIN keyword and EXCEPTION.
For example, in this block, do_something will be executed when the execution of some_procedure raise an exception:
<pre>BEGIN
some_procedure;
EXCEPTION
do_something;
END;</pre>
What exactly do you want to accomplish ? do you want to catch errors when executing the insert ?
For example, if you want to do something when the insert fails because the record you are trying to insert already exists in the table, you could do something like this:
<pre>BEGIN
insert into table_x ......
EXCEPTION
when dup_val_on_index then
dbms_output.put_line("DUPLICATE RECORD !");
END;</pre>
If that is not what you meant, please explain a little more your question.
To see all answers submitted to the Answer Wiki: View Answer History.
HI,
I need to perform a Unique check on existing record in table X for combination of field in my PL/SQL block before
inserting new record into Table XX
Table XX for combination of the following field list
(X_ID, St1, St2, City, CP, PS, XNAME, C_ID) AND DISPLAY MODE = 0
How can i do this check?? yes i need to raise an exception if this set of record is already existing how can i accomplish this. this exception can be raised only after insert statement i think as u said ,could u pls guide me with this
If a unique index exist on those columns, then you could use the dup_val_on_index pre-defined exception as in the example above.
If there is no unique index, you should perform a query to see if that combination of fields already exist in the table. Something like:
select 1 into l_exists from table_X where X_ID = X_ID_value and St1 = St1_value and St2 = St2_value and... ... etc... if nvl(l_exists,0) = 0 then insert into table_X ... else dbms_output.put_line('duplicate record !'); end if;Carlos has covered this well. Just a couple of notes.
If you do not already have a Unique constraint on the table, you can add one as:
ALTER TABLE Table_XX
ADD CONSTRAINT MyUniqueConstraint UNIQUE (X_ID, St1, St2, City, CP, PS, XNAME, C_ID, DISPLAY_MODE);
Then, you will automatically get a constraint violation exception if you attempt to insert a record that already has that combination of values. (Note that if any dups already exist, you will not be able to create the constraint.)
I am not sure what your “…AND DISPLAY MODE = 0″ clause is intended to do. Is this a PL/SQL variable that you are testing (rather than another column in the table)? If you only want the exception to occur when this variable is 0, then you will need to use the “select” query that CarlosDL showed below, and combine it with the variable value.
Thanks both of u ,it answered my query and thanks its compiling fine now
tell me briefly about pragma exception_init