1. Overview
This document will be helpful to insert data from intermediate table to source table with error log table
2. Technologies and Tools Used
- Oracle Plsql code.
3. Use Case
Load data from excel to intermediate table and then insert to base table using below code
Step1: Run below code.
Code:
DECLARE
lv_sql CLOB;
lv_col VARCHAR2(32000);
lv_val_col VARCHAR2(32000);
lv_cons Varchar2(50);
BEGIN
BEGIN
EXECUTE IMMEDIATE ‘drop table err$_’ || :target_table;
dbms_errlog.create_error_log(dml_table_name => :target_table);
EXCEPTION
WHEN OTHERS THEN
dbms_errlog.create_error_log(dml_table_name => :target_table);
END;
lv_sql := ‘ Create or replace Procedure csv_insert_pr
as
TYPE row_ty IS
TABLE OF ‘
|| :Source_table
|| ‘%rowtype INDEX BY PLS_INTEGER;
lv_all row_ty;
BEGIN
SELECT
*
BULK COLLECT
INTO lv_all
FROM ‘
|| :Source_table
|| ‘;
–dbms_output.put_line(lv_all.count);
FORALL i IN lv_all.first..lv_all.last
INSERT INTO ‘
|| :target_table
|| ‘( ‘;
FOR j IN (
SELECT
column_name
FROM
user_tab_cols
WHERE
upper(table_name) = upper(:target_table)
) LOOP
if lv_cons is null then
Select
COLUMN_NAME
into
lv_cons
from ALL_CONS_COLUMNS
WHERE upper(table_name) = upper(:target_table)
and OWNER=upper(:p_schema);
end if;
lv_col := lv_col
|| ‘,’
|| j.column_name;
If (lv_cons=j.column_name and :p_sequence is not null ) then
lv_val_col := lv_val_col
||’,’|| :p_sequence
||’.Nextval’;
else
lv_val_col := lv_val_col
|| ‘,lv_all(i).’
|| j.column_name;
end if;
END LOOP;
lv_sql := lv_sql
|| substr(lv_col, 2)
|| ‘ ) VALUES ( ‘
|| substr(lv_val_col, 2)
|| ‘) LOG ERRORS INTO err$_’
|| :target_table
|| ‘ REJECT LIMIT UNLIMITED;
END csv_insert_pr;’;
EXECUTE IMMEDIATE lv_sql;
dbms_output.put_line(‘execute’);
END;
/
Calling Code:
begin
csv_insert_pr;
end;
4. Screen Shot
Output:
