PL/SQL: How to use EXCEPTION statement before INSERT?

610 pts.
Tags:
Database programming
EXCEPTION statement
INSERT statement
PL/SQL
PL/SQL block
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Inprise
    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
    610 pointsBadges:
    report
  • carlosdl
    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;
    
    69,835 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Inprise
    Thanks both of u ,it answered my query and thanks its compiling fine now
    610 pointsBadges:
    report
  • Pava
    tell me briefly about pragma exception_init
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following