Check if INSERT was successful

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 successful 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

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 2

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.