Forum Stats

  • 3,874,232 Users
  • 2,266,692 Discussions
  • 7,911,777 Comments

Discussions

Target table load and raise exceptions while loading

User_CS62G
User_CS62G Member Posts: 17 Red Ribbon

Hello All,

I am trying to write a PL/SQL code for ETL(Extract,Transform&Load). I have a mapping table where we have target table name, target table column names , target column metadata, source table name and source table column name. See below screenshot.

Now there are rules for loading the target table as below :

(i) Target columns for which MANDATORY column is marked as 'Y' and if <Source_Column> in Source table is NULL, then that source record should be written in an Exception table mentioning that <Source_Column> is NULL.

(ii) Target Columns for which MANDATORY is marked as 'Y' and we have DEFAULT value, then if the <Source_Column> in Source table is NULL then the record is written in Target table with DEFAULT value for that column and at the same time record should be written in Exception table mentioning that <Source_Column> is NULL

(iii) If <Source_Column> value cannot be inserted in <Target_Column>, record should be written in Exception table mentioning that <Source_Column> is incompatible with <Target_Column>.

(iv) If <Source_Column> datatype and <Target_Column> datatype are different and cannot be converted then, the record should be written in Exception table mentioning <Source_Column> incompatible data types.

How can we achieve the above (iii) & (iv) rule ? Please help me out with a performant solution.


Thanks,

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    Just create those rules as part of the target table ddl. Make columns not null or have a default value. Create an error log table so when you insert records into the table, any failures end up in the error log :

    -- Create error log table on table
    begin
      DBMS_ERRLOG.create_error_log ('MY_TABLE');
    end;
    
    -- Insert records logging errors
    insert into my_table 
    select * 
    from source_table
    log errors into err$_my_table('INSERT') reject limit unlimited;
    
    -- List errors
    select ora_err_number$, ora_err_mesg$
    from  err$_my_table
    where ora_err_tag$ = 'INSERT';
    
    User_CS62G
  • User_CS62G
    User_CS62G Member Posts: 17 Red Ribbon

    Hello Paul,

    Thanks for showing me the way. Most of the issue is solved. I read about the package DBMS_ERRLOG.CREATE_ERROR_LOG. From the error table, we can determine the row which has caused an issue using rowid logged in column ORA_ERR_ROWID$. 

    Now the target table can contain many columns, is there a way to determine which column of which record has caused an issue while loading.

    Please help.

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond

    If you're wanting to know all of the potential column errors, you won't get that from this approach. For example, if you had 10 "not null" columns and you tried to insert null into all of them, Oracle will only log the first column error it comes across. It won't bother checking any of the other columns and logging if they failed, it doesn't need to.

    ora_err_mesg$ will tell you the error raised by Oracle. If it was a column constraint issue, the error code will mention the column (the first one it found with an issue).