error/exception handling in oracle

Question

i want to develop a procedure for following scenario.

I have one source, one target and one error table. Target and Error tables have all fields that are present in source tables. But the data type of all fields for error table are varchar. Error table don't have integrity, foreign key and other constraints. Error table also have two more fields: Error no and error message.

Now when procedure is executed if there is error while inserting any record into target then that record shold be moved to error table. Also the data base error code and error message should be logged in the error tables fields as mentioned.

How can i devlop such a procedure?

Example of table schema:

source table  
    src(id number 
        ,name varchar2(20)  
        , ... )

target table  
    tgt(id number 
        ,name varchar2(20) not null 
        , ... )

error table  
    err (id varchar2(255) 
          ,name  varchar2(255)
          , ... 
          , errno varchar2(255)
          , errmsg varchar2(255))

Answer 1

Have you looked at Oracle's own error logging functionality?

http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14231/tables.htm#ADMIN10261

Answer 2

Procedures to do that could look like this:

procedure ins_tgt(p_id in number, p_name in varchar2, ...) is
  v_errno number; v_errmsg varchar2(2000);
begin
  insert into tgt(id, name, ...) values (p_id, p_name, ...);
exception
  when others then
    /* copy sqlcode and sqlerrm into variables since they can't be used directly in a SQL statement */
    v_errno := sqlcode;
    v_errmsg := sqlerrm;
    insert into err(id, name, errno, errmsg) values (p_id, p_name, v_errno, v_errmsg);
end;


procedure copy_src_tgt is
begin
  for s in (select * from src) loop
    ins_tgt(s.id, s.name, ...);
  end loop;
end;

but it seems like a horrible inefficient way to copy data from one table to another...

Answer 3

CREATE OR REPLACE PACKAGE BODY foo_dml IS

    PROCEDURE log_err (
        p_sqlcode IN NUMBER,
        p_sqlerrm IN VARCHAR2,
        p_src     IN foo%ROWTYPE
    ) IS
        -- inserts the input row to the err log
    BEGIN
        INSERT INTO err (
            errno,
            errmsg,
            ID,
            NAME,
            ...
        ) VALUES (
            p_sqlcode,
            p_sqlerrm,
            p_src.id,
            p_src.name,
            ...
        );
    END;

    PROCEDURE copy_to_tgt (
        p_src IN foo%ROWTYPE
    ) IS
        -- copies the input row to the tgt table
    BEGIN
        INSERT INTO
            tgt
        VALUES
            p_src;
    EXCEPTION
        WHEN OTHERS THEN
            log_err( SQLCODE, SQLERRM, p_src );
    END;

END;
/