Check if INSERT was succesful

Question

I have some procedure that perform INSERT statement:

CREATE OR REPLACE PROCEDURE potok_insert(
  p_jfplate IN potok.jfplate%TYPE,
  p_post IN potok.post%TYPE,
  p_jfchan IN potok.jfchan%TYPE,
  p_jfdatetime IN VARCHAR2 
  ) 
AS 
  t_jfdatetime TIMESTAMP:=TO_TIMESTAMP(p_jfdatetime,'DD.MM.YYYY HH24:MI:SS');
BEGIN
  INSERT INTO potok (jfplate, post, jfchan, jfdate_y, jfdate_m, jfdate_d, jftime, jfdatetime, 
    dt_reg, ibd_arx)
      VALUES (RTRIM(p_jfplate),
        p_post, 
        RTRIM(p_jfchan), 
        EXTRACT(YEAR FROM t_jfdatetime), 
        EXTRACT(MONTH FROM t_jfdatetime), 
        EXTRACT(DAY FROM t_jfdatetime), 
        LPAD(EXTRACT(HOUR FROM t_jfdatetime),2,'0')||':'||
        LPAD(EXTRACT(MINUTE FROM t_jfdatetime),2,'0')||':'||
        LPAD(EXTRACT(SECOND FROM t_jfdatetime),2,'0'), 
        CAST(t_jfdatetime AS DATE),
        SYSDATE,
        1);  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END potok_insert;

Some triggers and constraints are applied to table, they can break INSERT. How can I check in procedure body - if INSERT was succesful or not?

Of course I can call count() in head and in end of procedure, but this will be not so graceful solution.

Answer 1

When constraints are violated, an exception will be thrown and you are going to end up in your exception handling block.

What kind of triggers are preventing you from inserting? Can you throw an exception in there too?

Answer 2

You can use the RETURNING clause to return the rowid of the row you have just created, like this:

CREATE SEQUENCE seq_emp;

set serveroutput on

DECLARE
 x emp.empno%TYPE;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING empno
  INTO x;

  dbms_output.put_line(x);
END;
/

DECLARE
 r rowid;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING rowid
  INTO r;

  dbms_output.put_line(r);
END;
/

DECLARE
 x emp.empno%TYPE;
 r rowid;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING rowid, empno
  INTO r, x;

  dbms_output.put_line(r); 
  dbms_output.put_line(x);
END;

Taken from this link:

http://www.psoug.org/reference/insert.html

Answer 3

Remove the EXCEPTION...WHEN OTHERS.

Really the question should not be how to test to see if it succeeds, but a decision on what to do if it fails.

Answer 4

In the exception block you should dump the contents of SQLCODE and SQLERRM so you can see what error(s) you're getting. Perhaps adding the following to your WHEN OTHERS handler would help:

DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE ||
                     '  SQLERRM=''' || SQLERRM || '''');

Share and enjoy.

Answer 5

You can check the value of SQL%ROWCOUNT to find out if a row was actually inserted. I think it would be very bad practice to have a trigger which failed to (or decided not to) insert a record without raising an exception, but it could happen.

Also, your "rollback;" statement is redundant, and should be changed to something like this:

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE=' || SQLCODE ||
                     '  SQLERRM=''' || SQLERRM || '''');
RAISE;
END;

The RAISE command will raise the same exception that was caught from the trigger so that your calling program will know why it failed (in addition to the debugging of dbms_output).