Skip to Main Content
  • Questions
  • Best Practice: Does the SQL Mantra simplify Data Verification?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Duke.

Asked: September 08, 2005 - 9:18 am UTC

Last updated: February 01, 2018 - 11:11 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Tom-- I subscribe to your SQL mantra for data (and truly, is there anything else? :)) which is:

1. Do it in a single SQL statement if at all possible.
2. If you cannot, then do it in PL/SQL (as little PL/SQL as possible!).
3. If you cannot do it in PL/SQL, try a Java Stored Procedure
(extremely rarely necessary with Oracle9i and above.)
4. If you cannot do it in Java, do it in a C external procedure.
(when raw speed, or 3rd party API written in C is needed.)
5. If you cannot do it in a C external routine, you might want to seriously think
about why it is you need to do it...

as stated in the question "Sql or Pl/sql approach" at </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5500485045890
among other places.

I wonder: will you concur with one of my rationales for following the mantra, or have another suggestion? 

My rationale is that "SQL makes verification simpler." Some background: We're loading a data warehouse; here's the greatly simplified structure of each of the ETL programs:

CURSOR SELECT ... FROM SOURCE_TABLE
FOR REC IN CURSOR LOOP
INSERT INTO TARGET_TABLE
END LOOP

(I know this isn't consistent with your recommendation for "Pl/SQL ETL" @ 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8784259916366
but I inherited the code)

Next come the verification queries, which I am introducing. These have the form:

SELECT ... FROM SOURCE_TABLE
MINUS
SELECT * from TARGET_TABLE
/
SELECT * from TARGET_TABLE
MINUS
SELECT ... FROM SOURCE_TABLE
/

"SQL makes verification simpler" because it's very difficult to verify the correct operation of procedural code.  However, it's easy to verify the operation of the ETL using SQL (declarative code).  Would you concur?

[We recently discussed that SQL is probably as easy to program as PL/SQL, and the pitfalls of poor programming practices at 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5500485045890#47352208686203 <code>]


and Tom said...

You might want to glance at
</code> https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

Comparing the Contents of Two Tables

and follow the link to 
http://asktom.oracle.com/~tkyte/compare.html <code>in there to see a pretty neat way to compare the contents of two tables.


I agree totally that we can all

a) write procedural code that is not understandable by anyone (even ourselves)
b) write SQL that is not understandable by anyone (even ourselves)

that is what "comments" are about - similar to the way when I present a complex analytic example - I break it down and step through the logic, we called is "commenting the code" in the past (not javadoc commenting, real comments, explaining the thoughts behind the code, how the code works -- be it sql or procedural code, code is -- well code)

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Table comparison & SQL v PL/SQL

Duke Ganote, September 08, 2005 - 11:54 am UTC

The table comparison method looks pretty slick... I'll have to try that!

I also realize that my question may not be clear. I'm arguing for the advantage of using:

CURSOR SELECT [various SQL-based ETL operations] FROM source_table
FOR REC IN CURSOR LOOP
INSERT INTO target_table
END LOOP

rather than

CURSOR SELECT * FROM source_table
FOR REC IN CURSOR LOOP
[various PL/SQL-based ETL operations]
INSERT INTO target_table
END LOOP

because (after more thought) I can create a view:

CREATE VIEW source_to_target AS
SELECT [various SQL-based ETL operations] FROM source_table

which I can easily use in the ETL program:

CURSOR SELECT * FROM source_to_target
FOR REC IN CURSOR LOOP
INSERT INTO target_table
END LOOP

and with the more computationally-efficient comparison method you referenced to verify the ETL.

BTW: I prefer SQL over PL/SQL because "necessity is the mother...": in a previous job I was expressly prohibited from writing PL/SQL so they could hire cheaper contract ETL programmers and I could focus on design. Following this division-of-labor, I only wrote ETL specs for the PL/SQL programmers, and reviewed their work. For my review (and as part of the specs), I created verification views -- which meant some gnarly SQL. I got good enough at it that for one vital and complex ETL process, the PL/SQL programmers just gave up (after banging their heads for a couple of weeks) and used the "verification view" for their CURSOR rather than doing any transformation logic in PL/SQL. And as you noted, it was fast too! :)


Tom Kyte
September 08, 2005 - 4:07 pm UTC

I'd argue for

insert into target_table
select [various functions] from source_table;

myself.

but pack as much work into sql as you can.

Or even ...

Bob B, September 08, 2005 - 5:53 pm UTC

Or even

CREATE VIEW SOURCE_TO_TARGET AS ...

INSERT INTO TARGET
SELECT *
FROM SOURCE_TO_TARGET;



Extreme Programming: SQL

Duke Ganote, September 12, 2005 - 9:24 am UTC

About the time I was prohibited from writing PL/SQL -- but not SQL -- I was working with a developer who was into Extreme Programming (XP). One of the more useful practices from XP is "code the unit test first":
</code> http://www.extremeprogramming.org/rules/testfirst.html <code>
which begins: "When you create your tests first, before the code, you will find it much easier and faster to create your code."

I found that writing the verification / test SQL, then using that for the ETL programming was a great confluence of practices: XP and the SQL Mantra.

exceptions

Duke Ganote, December 11, 2005 - 6:12 pm UTC

The argument I've heard for slow-by-slow is that the precise row with an exception can be captured, and this doesn't impede a partial load.

My counter-argument is that the table load is a transaction, and a partially successful load isn't a transaction. Row-by-row may be useful for debugging aid at best. Your thoughts?

Tom Kyte
December 11, 2005 - 6:34 pm UTC

It can be useful to capture exceptions - but even then it need not be slow by slow, it can be "hundreds by hundreds" (array processing)

Array -- error capture

Duke Ganote, December 11, 2005 - 8:56 pm UTC

So tweaking this
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8912264456901#26442375091563 <code>(and your response to its original question) gives this counterpart to an "error-capturing" slow-by-slow, right?

************* SETUP SCRIPT ****************
create table source as
select mod(level,3) primary_key, level+.5 payload
from dual connect by level < 7
/
create table target as select * from source where rownum = 0
/
alter table target add constraint target_pk primary key ( primary_key )
/
create table target_err as select * from source where rownum = 0
/
CREATE OR REPLACE PROCEDURE test_bulk AS
type array is table of source%rowtype index by binary_integer;
data array;
errors NUMBER;
dml_errors EXCEPTION;
l_cnt number := 0;
l_err number;
PRAGMA exception_init(dml_errors, -24381);
cursor c is select * from source;
BEGIN
open c;
loop
fetch c BULK COLLECT INTO data LIMIT 100
begin
FORALL i IN 1 .. data.count SAVE EXCEPTIONS
insert into target values data(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
l_cnt := l_cnt + errors;
FOR i IN 1..errors LOOP
l_err := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
insert into target_err values data(l_err );
end loop;
end;
exit when c%notfound;
END LOOP;
close c;
dbms_output.put_line( l_cnt || ' total errors' );
end;
/

************* RESULTS (I'm using 10g) ****************

sql> exec test_Bulk;
3 total errors

PL/SQL procedure successfully completed.

sql> select * from target;

PRIMARY_KEY PAYLOAD
----------- ----------
1 1.5
2 2.5
0 3.5

sql> select * from target_err;

PRIMARY_KEY PAYLOAD
----------- ----------
1 4.5
2 5.5
0 6.5

Tom Kyte
December 12, 2005 - 7:51 am UTC

yes, and if you were using 10gR2 that could become



insert /*+ APPEND */ into t select * from t2
LOG ERRORS REJECT LIMIT UNLIMITED;


;)

Top of the 10gR2 Top 10

Duke Ganote, December 12, 2005 - 8:48 am UTC

Yep, that's what I'm looking for(ward too)!

A perhaps familiar author wrote: "This new feature is destined to be my favorite Oracle 10g Release 2 new feature for sure! We all know that performing large bulk operations rather than row by row processing is superior for speed and resource usage (let alone ease of coding) but error logging of failed rows has always been an issue in the past. No more with this new feature in place."

</code> http://download-east.oracle.com/oowsf2005/204wp.pdf <code>

THE MANTRA RULES! :)

Debugging

Duke Ganote, December 18, 2005 - 11:25 pm UTC

The biggest complaint I anticipate is that it's difficult to debug through a view, e.g.

  create table t ( x char(1) );
  insert into t values ( '1' );
  insert into t values ( 'A' );
  insert into t values ( '2' );
  create view t_vw as select to_number(x) from t;
  create table targetted as select * from t_vw 
    where rownum = 0;

  SQL> select * from t_vw;
  ERROR:
  ORA-01722: invalid number
  no rows selected

Pending update to 10gR2, would this be workable for fall-back debugging?

DECLARE
  rid VARCHAR2(20);
BEGIN
  INSERT INTO targetted
     SELECT X FROM t_vw;
EXCEPTION
  WHEN OTHERS THEN
    FOR rec IN ( SELECT rowid
                    FROM t ) LOOP
      rid := rec.rowid;
      BEGIN
        FOR onerec IN ( SELECT *
                        FROM t_vw
          WHERE rowid = rec.rowid ) LOOP
          INSERT INTO targetted
            SELECT X FROM t_vw
             WHERE rowid = rid;
        END LOOP;
      EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(rid||' bad');
    END;
  END LOOP;
END;
/
SQL> /
AAAN0oAAEAAAKDFAAB bad

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> select * from t;

X
-
1
A
2

SQL> select * from targetted;

         X
----------
         1
         2 

Tom Kyte
December 19, 2005 - 7:24 am UTC

I don't understand what you are saying?


forget views for a minute, the view in this example is a red herring, just use the SQL text itself.

You have a bad situation whereby someone opted to use a string to store a number.

clarification, I hope

Duke Ganote, December 19, 2005 - 2:37 pm UTC

I know the first part will work for all historical data:

INSERT INTO targetted
SELECT X FROM t_vw;

However, I wanted to have some exception-catching for the future unknowns; for example, when someone on the mainframe enters some new code value, like the 'A'. I was attempting to concoct code that would re-use the view (which encapsulates the transformation logic and is very fast) yet find exactly where the all-in-one INSERT statement failed (if it fails) yet still process any the good rows.

multi-table inserts are a wonder

Duke Ganote, December 31, 2005 - 8:08 am UTC

I've been re-writing some slow-by-slow 8i code, and it's a pleasure to replace this loopy, multi-insert snippet:

FOR rec IN ( SELECT * FROM SOURCE ) LOOP
SELECT seq_agreement_id.NEXTVAL
INTO v_nGendID
FROM DUAL;
SAVEPOINT Record_Start;
INSERT INTO agreement(
agreement_id, agreement_type_id,
period, last_update_date)
VALUES ( v_nGendID, v_agreement_type_id,
rec.date_stamp, SYSDATE);
INSERT INTO loan ( loan_agreement_id,
last_update_date, period, payload )
VALUES ( v_nGendID, SYSDATE,
rec.period, payload );
COMMIT;
END LOOP;

with a single, multi-table INSERT statement:

INSERT ALL
INTO agreement(
agreement_id, agreement_type_id,
period, last_update_date)
VALUES ( seq_agreement_id.NEXTVAL,
v_agreement_type_id, date_stamp, SYSDATE)
INTO loan ( loan_agreement_id,
last_update_date, period, payload )
VALUES ( seq_agreement_id.CURRVAL, SYSDATE,
period, payload )
SELECT period, payload
FROM source;

as discussed as </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6915127515933#7871897954847 <code>
I like 'tagging' all the rows in the warehouse with a unique id, but the slow-by-slow processing makes monthly processing unscalable. CTAS'ing a table with all 5 years of history is faster than slow-by-slow doing the current month's processing!

REJECT LIMIT UNLIMITED: 10gR2 & imitated before

Duke Ganote, January 15, 2006 - 11:15 pm UTC

I upgraded my laptop database to 10gR2, then found Natalka Roshah's exposition on DML Error Logging
</code> http://www.orafaq.com/node/76 <code>
Interesting that apparently "DML errors are logged as an autonomous transaction; rolling back the DML that spawned the error does not clear the error logging table."

My post of December 18, 2005 was intended to approximate that functionality for versions prior to 10gR2...well, without the autonomous aspect. Here I encapsulate the transformation call into a reuseable procedure within the outer procedure:

CREATE OR REPLACE
PROCEDURE inserter_with_error_capture AS
l_row# NUMBER; l_err# NUMBER; l_errmsg VARCHAR2(2000);
PROCEDURE transform_load ( p_row# IN NUMBER := NULL ) AS
BEGIN
INSERT INTO targetted ( x )
SELECT x FROM t_vw
WHERE row# = NVL(p_row#,row#)
;
END transform_load;
BEGIN
transform_load;
EXCEPTION
WHEN OTHERS THEN
FOR rec IN ( SELECT row# FROM t_vw ) LOOP
l_row# := rec.row#;
BEGIN
transform_load(l_row#);
EXCEPTION WHEN OTHERS
THEN --LOG ERRORS INTO t_vw_bad REJECT LIMIT UNLIMITED
l_err# := SQLCODE;
l_errmsg := SQLERRM;
INSERT INTO t_vw_bad ( ORA_ERR_NUMBER$, ORA_ERR_MESG$, ROW# )
VALUES ( l_err#, l_errmsg, l_row# );
END;
END LOOP;
END inserter_with_error_capture;


pipelined functions equivalent to 10gR2's DML Error Logging ?

Duke Ganote, February 02, 2006 - 4:50 pm UTC

The questions on pipelined functions, e.g.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19481671347143
raised a question:  Are pipelined functions with autonomous error logging equivalent to 10gR2's DML Error Logging?  You showed DML error logging with this sample construct:

insert /*+ APPEND */ into t select * from t2 
LOG ERRORS REJECT LIMIT UNLIMITED;

A pipelined function would be something like this (building on previous examples):

create or replace type T_scalar_type as object ( x number )
/
create or replace type T_table_type as table of T_scalar_type
/
create or replace procedure logger (
  p_err# number, p_errmsg VARCHAR2, p_inval VARCHAR2 )
 as
         pragma autonomous_transaction;
 begin
    INSERT INTO t_vw_bad ( ORA_ERR_NUMBER$, ORA_ERR_MESG$, X )
     VALUES ( p_err#, p_errmsg, p_inval );
 commit; -- essential to avoid ORA-06519
end logger;
/
create or replace function T_etl
 return T_table_type
 PIPELINED
as
  l_rec  T%rowtype;
  l_err# NUMBER;
  l_errmsg VARCHAR2(2000);
begin
  for rec in ( select * from T ) loop
     <<exception_catcher>> BEGIN
       l_rec.x := TO_NUMBER(rec.x);
        pipe row( T_scalar_type( l_rec.x ) );
     EXCEPTION
        WHEN OTHERS THEN
          l_err# := SQLCODE;
          l_errmsg := SQLERRM;
          logger ( l_err#, l_errmsg, rec.x );
     END exception_catcher;
  end loop;
  return; 
end T_etl;
/

SQL> select * from t;

X
-
1
A
2

SQL> select * from table(t_etl);

         X
----------
         1
         2

SQL> select * from t_vw_bad;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
---------------
X
-------------------
          -6502
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
A

I probably should benchmark them... (with more than 3 records :)  Clearly, 10gR2's capability is far simpler to code. 

Tom Kyte
February 03, 2006 - 1:40 pm UTC

nope, not even close.

so you pipe out a row and that row fails in the insert due to a check constraint. game over.


DML error logging is much more than a simple pipelined function. the pipelined function MIGHT be able to deal with simple things like "number field is not a number" and such - but not "in general"


Add a check constraint to your table T, something like "check (x<0)"

DML Error Logging

Deepak Haldiya, February 04, 2006 - 7:15 pm UTC

Hi Tom,

I am running this following test on table SCOTT.EMP, on Ora10gR2:

ora10g> select empno, ename, sal, hiredate from emp where empno in (7934, 7900) ;

EMPNO ENAME SAL HIREDATE
---------- ---------- ---------- ---------
7900 JAMES 950 03-DEC-81
7934 MILLER 1300 23-JAN-82

EMPNO is primary column.

ora10g> exec dbms_errlog.create_error_log( 'EMP' );
ora10g> insert into emp(empno, ename) values (7934, 'MILLER');

insert into emp(empno, ename) values (7934, 'MILLER')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMP_PK) violated

ora10g> insert into emp(empno, ename) values (7934, 'MILLER') log errors reject limit unlimited ;

0 rows created.

ora10g> update emp set empno = 7900 where empno = 7934 log errors reject limit unlimited ;
update emp set empno = 7900 where empno = 7934
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMP_PK) violated

For the Insert Statement, the error gets logged into the Error Table but the same does not happen for Update Statement. Per Oracle Manual, Error Logging feature works same on all four DML Statements (Insert, Update, Merge, Delete).

Why does the error looging clause behave different in the above Update Statement?

Thanks A Lot
Deepak



Tom Kyte
February 06, 2006 - 12:21 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#SQLRF20000 <code>

<quote>

# The following conditions cause the statement to fail and roll back without invoking the error logging capability:

* Violated deferred constraints.
* Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
* Any update operation UPDATE or MERGE that raises a unique constraint or index violation).



INSERT...LOG ERRORS INTO + ROLLBACK

Brian_Camire, February 16, 2006 - 1:09 pm UTC

The article by Natalka Roshak at
</code> http://www.orafaq.com/node/76
that Duke mentioned in his January 15 post says:

"It seems that DML errors are logged as an autonomous transaction; rolling back the DML that spawned the error does not clear the error logging table."

As it turns out, this is the behavior I want, but can you point out where this behavior is documented?

I did find a passage at
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#InsertDMLErrorLogging <code>
that reads:

"If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far."

However, nowhere (that I can find) does it specify what happens if the statement does not exceed the reject limit and you subsequently roll back the transaction explicity. Based on the passage above, I suppose it's not unreasonable to assume that the log entries are retained then too, but is this explicitly documented anywhere?

Thanks.

Tom Kyte
February 17, 2006 - 1:17 pm UTC

I believe that covers it as much as it is covered. They are telling you "even if it rolled back - they are there, they are committed as we go along"


If you believe this should be more strongly documented, you'd have to open an iTar to get that into the system.

INSERT...LOG ERRORS INTO + ROLLBACK

Brian Camire, February 17, 2006 - 2:49 pm UTC

Thanks for your feedback.

I do think it needs to be explicitly documented, but I'm on XE, so the iTar route is not an option. I have posted a request on the XE forum at </code> http://forums.oracle.com/forums/thread.jspa?threadID=363703 <code>in the hope that something can be done, even though it's not an XE-specific issue.

Hope that's OK.

Thanks again.

Tom Kyte
February 17, 2006 - 5:18 pm UTC

you can rather trust that if it survives an automagic rollback - it'll survive your rollback as well - it (the error log) is committed as it goes along.

I'll file a doc bug.

INSERT...LOG ERRORS INTO + ROLLBACK

Brian Camire, February 17, 2006 - 5:25 pm UTC

Thanks.

Going back to 9iR2

Krous, April 04, 2006 - 2:46 pm UTC

Going back to 9iR2 where we unfortunately dont have log errors, what would you say is the best approach to do this kind of Processing ?

1. Read 100 records from external Table( with VARCHAR2(4000) fields) into array
2. Do insert (for 100 records) with a single but complex ( with Subqueries etc) SQL into a staging table with all columns set with not null constraints and proper type number/varchar2 and sizes as the final table
3. Process Bulk exceptions to find which constraint failed and why -- Process each subquery one by one to find which failed-no data found/multiple rows/etc( as bulk exceptions wont say which constraint failed *struggling for a better idea*..) and log ***lengthy process***
4. Loop till all records in external table are done
5. Insert (append) into final/interface table all records present in staging table
6. Truncate staging table

Any better ideas?

In one of your dozen or so topics on Bulk Collect/ETL etc you talk about validating the data first and then inserting to the table.I am guessing it means something more like
1. Bulk collect data from external table to array
2. Loop (i) Process each field to get the final output. conversion of legacy code etc and run through validation
(ii)Log errors into table
3. Bulk insert into final interface table all records without errors.
4. Loop till external table is read completely

Would this be faster? We dont have a second insert into a staging table but we would have to process field by field for all records rather than just for records with errors.

Tom Kyte
April 04, 2006 - 7:39 pm UTC

to simulate log errors you would

a) bulk fetch N rows
b) bulk insert N rows with save exceptions
c) log the exceptions with a message into table


that would do the same logically as log errors.

Continuation from previous post

Krous, April 04, 2006 - 3:30 pm UTC

..
How else would you approach the problem of any standard ETL
Read a given flat file.
Do conversion for each field on flat file( validating and transforming)
Log errors
Insert into table.
.
.
Reading
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:19481671347143 <code>
I am now even more confused, would Pipelined functions work better here?

Followon

Krous, April 05, 2006 - 1:21 am UTC

Sorry I was trying to bring this thread back to the original question
Your reply
****to simulate log errors you would

a) bulk fetch N rows
b) bulk insert N rows with save exceptions
c) log the exceptions with a message into table
that would do the same logically as log errors
****
provides a method of mimicking Log Errors but does not really help.

Perhaps my fault is not having better described the ETL/Interface processes...
Normally tables ( in APPS/SAP etc environments and even in some DWH systems) could have attribute/descriptive fields which would provide further information about the row but is not essentially required...
Example your review form has the Email field which is not really required, but if it is entered you would want this entered in the table. It is not possible to have/enable a constraint on this field since you might have 100k+ records where email field would be null. Since exceptions are thrown only when database constraints are not met I am at a quandry.
Since I knew these fields would always be required in the data that I am bringing over to the table, my solution had the creation of a temp table on which all constraints are created, collecting bulk exceptions logging and then passing on the scrubbed/transformed data to the final table... Do you have any recommendations/insightto this process ?

Thinking it through, I was lucky in this instance in knowing all the fields being sent my way are non null columns. It could be that a field is not mandatory but that if it is present in the incoming file I need to transform/scrub it, if not leave it null. How would you handle this situation?
Considering that this is standard ETL, I am interested in knowing what the best practices are ( and I believe Duke the OP eludes to this but it is not really clear... )All client solutions I have seen so far in the last 3 years ( perhaps as Mikito would point out due to my being in the Oracle Apps field where no "serious" work is done..) uses Row by Row processing. I would like to recode/propose the bulk collect method but am struggling to understand what process people should/would follow.

Tom Kyte
April 05, 2006 - 5:42 pm UTC

I read "do not have log errors", so sort of assumed "i want to have log errors". It was the reference to log errors (which is apparently something you would not be using if you had it!) that was confusing....

I don't really follow paragraph 2 here. the one beginning "perhaps my fault..."

If there are no exceptions tripped, I'm not sure what would happen?


How can you load directly into SAP tables - do they not have their own LOAD APIS you have to go through (we do for our applications for example)

*Interface*

Krous, April 05, 2006 - 9:30 pm UTC

Guess I am just confusing people with long-winded explanation.

Input file 3 columns. A, B, C present in external Table ext_a

I need to load this into Table XX ( which before run contains say some 100k records or so) where
A translates to AA using lookup table XA
B translates to BB using lookup table XB
C translates to CC using lookup table XC

A,B are required fields on Table XX and are enforced using constraints (not null)
C if provided on the needs to be converted to CC else will be present in final table as null.

my single insert and the best approach is

insert /*+ APPEND */ into XX
(
select ( select lookup from XA where id = ext_A.A ) AA,
( select lookup from XB where id = ext_A.B ) BB,
( select lookup from XC where id = ext_A.C ) CC
from ext_A

Problem is - if this errors out, (i) Do not know which record errored out. (ii) Rollback of all work done so far

Since row by row is the worst approach, we look at bulk collect -
cursor c is select * from ext_A
begin
open C;
loop;
fetch c bulk collect into <<array>> limit 100;
for i in 1.. <<array>>.count loop
begin
select lookup into AA from XA where id = ext_A.A ;
exception
when too_many_rows then <<log error>>
when no_data_found then <<log error>>
when xxx then << log errors >>
..
etc
...
<<create array B with clean data
end loop
forall insert clean data present in array B into final table
save exceptions
<<log exceptions again>>

This is the common solution provided utilizing bulk collect. Here
(i) performance is better than row by row processing and inserting
(ii) However we are still doing within the inner loop row by row processing ( hitting all lookups /processing table multiple times for something that can be done as single insert) and so performance is worse than single insert

Does the above provide the only solution utilizing bulk collect ( wherien i am still able to log errors by row) ?

The three (above 2 and the row by row processing) seem to be the only solutions people are aware of...

What seems via psuedo code as the best approach ( this is after I posted on ASKTOM Day before yesterday and since which I must have read every single ETL/Bulk post/thread on Asktom) to my mind is

open c;
loop
fetch c bulk collect into <<array>> limit 100;
forall i in 1..<<array>>.count save exceptions
insert when AA is not null and BB is not null and ((CC is null and C is null) or (C is not null and CC is not null) then
into XX
( A,B,C)
values
(AA,BB,CC)
when AA is null then
into <<error_table>>
(pk,table,err_msg)
values
(A, 'XX', "NO_DATA_FOUND")
..
..
( select <<array>>.AA, <<array>>.BB, <<array>>.CC from dual)
exceptions
process DML_ERRORS to find TOO_MANY_ROWS errors etc

seems perfect except for
(i) cannot access bulk in-bind variables error
(ii) Do not know for certain whether this approach is scalable , and whether this would be preferred method of processing such records as I have yet to find an Oracle Apps Tech consultant who has ever preferred working with bulk collect over row by row processing and has implemented the same on a production system................

I do not mind reading/researching alternate technologies/options etc if you could point them out to me ( Pipelined functions are an interesting concept but logically the above should be even faster as we would be require a similar array etc and we skip the casting to a table ). What I would like to do is get the best practice standard( and tools available) for the above straightforward problem and thus gain the neccessary ability to visualize a solution to a interface/ETL problem similar to how understanding how Analytical functions work I can visualize SQL statements for nearly every single type of report
Note: I am on 9iR2

Tom Kyte
April 06, 2006 - 9:57 am UTC

why not outer join to the look ups using the external table and bulk collecting THAT and forall i inserting???

I would not use a scalar subquery here - outer join seems to be right.

LOG ERRORS clause

Stewart Bryson, March 26, 2007 - 11:08 am UTC

Tom said
>> insert /*+ APPEND */ into t select * from t2
>> LOG ERRORS REJECT LIMIT UNLIMITED;

The only constraint on the table was a primary key. I don't believe this would work because the APPEND hint would cause the statement to fail on a unique constraint.

Also... more about the LOG ERRORS clause. Mark Rittman gave a test case with LOG ERRORS where it sometimes under performed compared to cursor-based approaches. You can find it here:

http://www.rittmanmead.com/2005/12/04/performance-issues-with-dml-error-logging-and-conventional-path-inserts/

In that posting, he mentions that he showed the results to you and you were concerned. Did anything ever come of that? Did you find out the "issue" if one existed?

Mark mentioned he opened an SR, but he hasn't responded to me about the result (if any) from that.

Thanks.




Tom Kyte
March 26, 2007 - 2:06 pm UTC

I did a full up presentation at hotsos last year (2006) about this - showing the different modes - what they did, what price there was to pay - yes...


Link to the presentation?

Stewart Bryson, March 26, 2007 - 2:42 pm UTC

Wouldn't happen to have a link to the presentation (paper, slides, etc.) would you? I'm guessing Hotsos doesn't want that stuff getting out for free, so do you know where else I can get this information? The documentation is a little dry.

Thanks.
Tom Kyte
March 27, 2007 - 9:17 am UTC

goto the files tab

it is named dml_error_logging.zip

just found it here... thanks

Stewart Bryson, March 26, 2007 - 2:44 pm UTC


modifications to default table design

Stewart Bryson, March 26, 2007 - 4:06 pm UTC

After reading your presentation, I see the basis behind the ORA_ERR_TAG$ column. Until this point, I've been doing the following. It works... but I wasn't sure if I was asking for trouble:

SQL> create table test1 as select * from dba_users;

Table created.

Elapsed: 00:00:00.14
SQL> exec dbms_errlog.create_error_log('TEST1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL> ALTER TABLE err$_test1
  2        ADD ( entry_ts TIMESTAMP DEFAULT systimestamp,
  3              entry_session NUMBER DEFAULT sys_context('USERENV','SESSIONID'),
  4              entry_module VARCHAR2(48) DEFAULT sys_context('USERENV','MODULE'),
  5              entry_action VARCHAR2(32) DEFAULT sys_context('USERENV','ACTION'));

Table altered.

Elapsed: 00:00:00.03
SQL> alter table test1 add constraint test1_uk1 unique(username);

Table altered.

Elapsed: 00:00:00.04
SQL> insert into test1 select * from dba_users;
insert into test1 select * from dba_users
*
ERROR at line 1:
ORA-00001: unique constraint (SWBRYSON.TEST1_UK1) violated


Elapsed: 00:00:00.09
SQL> insert into test1 select * from dba_users log errors;
insert into test1 select * from dba_users log errors
*
ERROR at line 1:
ORA-00001: unique constraint (SWBRYSON.TEST1_UK1) violated


Elapsed: 00:00:00.10
SQL> insert into test1 select * from dba_users log errors reject limit unlimited;

0 rows created.

Elapsed: 00:00:00.09
SQL> select entry_ts, entry_session, entry_action, entry_module from err$_test1;

ENTRY_TS                                                                    | ENTRY_SESSION | ENTRY_ACTION                     | ENTRY_MODULE
--------------------------------------------------------------------------- | ------------- | -------------------------------- | ------------------------------------------------
03/26/2007 03.54.10.936687 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.084105 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.086348 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.087889 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.089305 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.090694 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.092196 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.093650 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.095030 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.096419 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.097791 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.099154 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.100508 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.101888 PM                                               |         21431 |                                  | SQL*Plus
03/26/2007 03.54.40.103386 PM                                               |         21431 |                                  | SQL*Plus

15 rows selected.

Elapsed: 00:00:00.30
SQL> 

10g DML Error Logging Problems

Steve Mangan, March 28, 2007 - 6:52 am UTC

Tom

Under 10g we experience different results for INSERT and MERGE with DML Error Logging.
We are using version 10.2.0.3.0.

However from 10g Documentataion for the MERGE statement:

"error_logging_clause

The error_logging_clause has the same behavior in a MERGE statement as in an INSERT statement.
Please refer to the INSERT statement error_logging_clause for more information."

We find a difference when the primary key constraint is enforced using a non-unique index
bewteen insert and merge operation of the same data sets.

The actual application is a Datawarehouse where we are merging data from staging tables into
datastore tables.

The source tables have the same structure as the datastore tables which have a primary key which we match on.

The example has the following results. The insert behaves as expected(?) but not the merge.
We also test similar cases where the target table is empty and found differences.
The behaviour is different if a unique index is used to enforce the primary key constraint.
Why are ids 6 and 7 reported as errors under merge but not 8.

What are your comments, is this a bug.

BEFORE
======
SRC TARGET
---- ------
4 1
5 2
6 3
7 4
8 5
20
20

AFTER MERGE
===========
TARGET ERROR
------ -----
1 6
2 7
3 20
4
5

AFTER INSERT
============
TARGET ERROR
------ -----
1 4
2 5
3 20
4
5
6
7
8
20

Test case is for merge. Comment back in insert for its results. Also append hint can be added or
removed as required, as can index unique or not.

The following test case shows the behaviour.

set serveroutput on;

drop table err$_scm_dml;

drop table scm_dml;

create table scm_dml
as
select rownum id , object_name value
from all_objects
where rownum<=5;

drop table scm_dml_src;

create table scm_dml_src
as
select rownum + 3 id , object_name value
from all_objects
where rownum <= 5;

insert into scm_dml_src( id, value ) values ( 20, 'Twenty' );
insert into scm_dml_src( id, value ) values ( 20, '20' );

commit;

create index scm_dml_pk on scm_dml (id);

--create unique index scm_dml_pk on scm_dml (id);

ALTER TABLE scm_dml ADD (
CONSTRAINT scm_dml_pk
PRIMARY KEY
(id)
USING INDEX);

begin
dbms_errlog.CREATE_ERROR_LOG('SCM_DML');
end;
/

--begin
--insert /*+ append */
--into scm_dml
--select *
--from scm_dml_src
-- LOG ERRORS INTO ERR$_scm_dml ( TO_CHAR(sysdate,'dd-mon-yyyy hh24:mi:ss') ) REJECT LIMIT UNLIMITED;
-- exception
--when others then
--dbms_output.put_line(SUBSTR(sqlerrm,1,80)); -- Show that we encountered a unique constraint violation
--end;
--/

begin
merge /*+ APPEND */
into scm_dml tgt
using
(select * from scm_dml_src) src
on ( tgt.id = src.id )
when matched then
update set
tgt.value = src.value ||'X'
when not matched then
insert
( id, value )
values
( src.id, src.value )
LOG ERRORS INTO ERR$_scm_dml ( TO_CHAR(sysdate,'dd-mon-yyyy hh24:mi:ss') ) REJECT LIMIT UNLIMITED;
exception
when others then
dbms_output.put_line(SUBSTR(sqlerrm,1,80)); -- Show that we encountered a unique constraint violation
end;
/


commit;

select *
from scm_dml;

select *
from scm_dml_src;

select *
from err$_scm_dml
;


ID VALUE
1 I_CDEF1
2 IND$
3 FILE$
4 UNDO$
5 I_CON1


ID VALUE
4 I_CDEF1
5 IND$
6 FILE$
7 UNDO$
8 I_CON1
20 Twenty
20 20

ORA_ERR_NUMBER$|ORA_ERR_MESG$|ORA_ERR_ROWID$|ORA_ERR_OPTYP$|ORA_ERR_TAG$|ID|VALUE
1|ORA-00001: unique constraint (DWH.SCM_DML_PK) violated||I|28-mar-2007 11:42:30|20|Twenty
1|ORA-00001: unique constraint (DWH.SCM_DML_PK) violated||I|28-mar-2007 11:42:30|6|FILE$
1|ORA-00001: unique constraint (DWH.SCM_DML_PK) violated||I|28-mar-2007 11:42:30|7|UNDO$

Tom Kyte
March 28, 2007 - 12:06 pm UTC

the behavior is the same - upon error, log it.

it would just seem here they are getting different errors.


I'm not really sure what we are looking for precisely here. A good test case would be very "linear" (eg: those comments - don't know what to make of that)


INSERT and MERGE DML Error Logging Differ

Steve Mangan, March 29, 2007 - 5:20 am UTC


Tom

Apologies if my earlier example was not clear. Let me try again.

We are running Oracle 10.2.0.3.0. under windows.

I have a simple source table with 8 rows in it, ids 1,1,2,3,3,4,5,5.
I have 2 target tables( TGT_1 and TGT_2), both empty.
Each has a primary key constraint on the id column.
Both primary keys are enforced by *non-unique* indexes.
I insert into TGT_1 with DML error logging.
I merge into TGT_2 on the id with DML error logging.
As both target tables are empty I expected both TGT_1 and TGT_2 to be loaded with 5 rows,
ids 1,2,3,4 and 5 with 3 rows logged to their error tables, ids 1,3 and 5.
This was not the case.
TGT_1 had 5 rows with 3 errors logged in its error table : as expected.
TGT_2 had 0 rows with *7* errors logged in its error table : not expected.

Script ran and results were :

set serveroutput on;

drop table err$_tgt_1;

drop table tgt_1;

create table tgt_1
as
select rownum id , object_name value
from all_objects
where 1=0;

drop table err$_tgt_2;

drop table tgt_2;

create table tgt_2
as
select rownum id , object_name value
from all_objects
where 1=0;

drop table src;

create table src
as
select rownum id , object_name value
from all_objects
where rownum <= 5;

insert into src( id, value ) values ( 1, '1' );
insert into src( id, value ) values ( 3, '3' );
insert into src( id, value ) values ( 5, '5' );

commit;

create index tgt_1_pk on tgt_1 (id);

create index tgt_2_pk on tgt_2 (id);


ALTER TABLE tgt_1 ADD (
CONSTRAINT tgt_1_pk
PRIMARY KEY
(id)
USING INDEX);


ALTER TABLE tgt_2 ADD (
CONSTRAINT tgt_2_pk
PRIMARY KEY
(id)
USING INDEX);

begin
dbms_errlog.CREATE_ERROR_LOG('TGT_1');
dbms_errlog.CREATE_ERROR_LOG('TGT_2');
end;
/

begin
insert
into tgt_1
select *
from src
LOG ERRORS INTO ERR$_tgt_1 ( TO_CHAR(sysdate,'dd-mon-yyyy hh24:mi:ss') ) REJECT LIMIT UNLIMITED;
exception
when others then
commit;
dbms_output.put_line(SUBSTR(sqlerrm,1,80)); -- Show that we encountered a unique constraint violation
end;
/

begin
merge
into tgt_2 tgt
using
(select * from src) src
on ( tgt.id = src.id )
when matched then
update set
tgt.value = src.value ||'X'
when not matched then
insert
( id, value )
values
( src.id, src.value )
LOG ERRORS INTO ERR$_tgt_2 ( TO_CHAR(sysdate,'dd-mon-yyyy hh24:mi:ss') ) REJECT LIMIT UNLIMITED;
exception
when others then
commit;
dbms_output.put_line(SUBSTR(sqlerrm,1,80)); -- Show that we encountered a unique constraint violation
end;
/


commit;

select *
from src
order by id;

select *
from tgt_1
order by id;

select *
from err$_tgt_1
order by id
;

select *
from tgt_2
order by id;

select *
from err$_tgt_2
order by id
;

Results :

Table dropped.
Table dropped.
Table created.
Table dropped.
Table dropped.
Table created.
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
Commit complete.
Index created.
Index created.
Table altered.
Table altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
ORA-00001: unique constraint (DWH.TGT_2_PK) violated
PL/SQL procedure successfully completed.
Commit complete.

ID VALUE
---------- ------------------------------
1 I_CDEF1
1 1
2 IND$
3 FILE$
3 3
4 UNDO$
5 5
5 I_CON1


8 rows selected.

ID VALUE
---------- ------------------------------
1 I_CDEF1
2 IND$
3 FILE$
4 UNDO$
5 I_CON1


5 rows selected.

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (DWH.TGT_1_PK) violated

I
29-mar-2007 10:05:43
1
1

1

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ORA-00001: unique constraint (DWH.TGT_1_PK) violated

I
29-mar-2007 10:05:43
3
3

1
ORA-00001: unique constraint (DWH.TGT_1_PK) violated

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------

I
29-mar-2007 10:05:43
5
5



3 rows selected.
no rows selected.

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

I
29-mar-2007 10:05:43
1
1

1

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

I
29-mar-2007 10:05:43
1
I_CDEF1

1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------

I
29-mar-2007 10:05:43
2
IND$

1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated


ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
I
29-mar-2007 10:05:43
3
FILE$

1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

I

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
29-mar-2007 10:05:43
3
3

1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

I
29-mar-2007 10:05:43

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
4
UNDO$

1
ORA-00001: unique constraint (DWH.TGT_2_PK) violated

I
29-mar-2007 10:05:43
5

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------------------
ID
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
I_CON1



7 rows selected.


I then reran the test with *unique* indexes enforcing the primary keys.
This time both TGT_1 and TGT_2 have 5 rows inserted with 3 rows in their error tables - expected behaviour.

Is this a bug, it appears INSERT and MERGE DML error logging differ when a non-unique or uniques index enforces a primary key.

Thanks

Steve
Tom Kyte
March 30, 2007 - 12:31 pm UTC

the error logging does not "differ", they both logged errors.

The processing is fundamentally different here - merge and insert are "similar in some respects, different in others"


I do agree that the non-duplicated rows should probably (almost certainly) be inserted with the merge.

Can you file a bug - or if not - let me know and I will.

Further MERGE Problems

Steve Mangan, April 19, 2007 - 10:21 am UTC

Tom

We are on
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

for windows.

We have raised the above as a bug with Oracle, however, discussions are still on-going, their latest update indicated that this was the expected behaviour.

I now have another 10g feature that does not work correctly. If I merge append without a when matched clause (ie insert only) the rows are inserted but the unique primary key index is not updated.

Test case attached. We have a table with 1 row and merge (insert) 150 rows into it. Afterwards counts on the table show as 1 if the unique index is full scanned or 151 if a full table scan is done. The data is in the table correctly just the index is incorrect.

As a work around either running the merge in non append mode, or adding a when matched clause seem to work.


DROP SEQUENCE SCM_SEQ_CLAIMS_STATE;

CREATE SEQUENCE SCM_SEQ_CLAIMS_STATE
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  CACHE 20
  NOORDER;

drop table scm_d_claim_state ;

CREATE TABLE SCM_D_CLAIM_STATE
(
  CLMS_ID        NUMBER(10) NOT NULL,
  STATUS         VARCHAR2(127 BYTE) NOT NULL,
  CLAIM_TYPE     VARCHAR2(127 BYTE) NOT NULL,
  INJURY_LOOKUP  VARCHAR2(127 BYTE),
  INJURY         VARCHAR2(127 BYTE) NOT NULL,
  COVER          VARCHAR2(127 BYTE) NOT NULL,
  COMP_NON_COMP  VARCHAR2(127 BYTE),
  DAMAGE_TYPE    VARCHAR2(127 BYTE),
  ETL_BATCH_NO   NUMBER
) noparallel
;

INSERT INTO SCM_D_CLAIM_STATE ( clms_id, status,claim_type,injury,cover ) values ( -1,'Unknown','Unknown','Unknown','Unknown' );

commit;

alter table scm_d_claim_state add constraint scm_d_claim_state_pk primary key ( clms_id );

select count(*) from scm_d_claim_state;

select * from scm_d_claim_state;
 
  merge
   /*+ APPEND */
  INTO scm_d_claim_state dcs USING
    (
    SELECT  
       TO_CHAR( object_id + 10000000 ) status,
       TO_CHAR( object_id + 20000000 ) claim_type,
       TO_CHAR( object_id + 30000000 ) injury,
       TO_CHAR( object_id + 40000000 ) cover,
       TO_CHAR( object_id + 50000000 ) injury_lookup,
       TO_CHAR( object_id + 60000000 ) comp_non_comp,
       TO_CHAR(object_id +  70000000 ) damage_type 
  FROM all_objects where rownum<=150     )
  c_rows ON( c_rows.status = dcs.status AND c_rows.claim_type = dcs.claim_type AND c_rows.injury = dcs.injury AND c_rows.cover = dcs.cover)
WHEN NOT matched THEN
  INSERT( clms_id, injury_lookup, comp_non_comp, damage_type, status, claim_type, injury, cover, etl_batch_no)
  VALUES( scm_seq_claims_state.NEXTVAL, c_rows.injury_lookup, c_rows.comp_non_comp, c_rows.damage_type, c_rows.status, c_rows.claim_type, c_rows.injury, c_rows.cover, -2 )
--WHEN MATCHED THEN
--UPDATE SET
--injury_lookup = c_rows.injury_lookup,
--comp_non_comp = c_rows.comp_non_comp,
--damage_type = c_rows.damage_type,
--etl_batch_no = -1   
;
 
commit;

select * from scm_d_claim_state;

select count(*) from scm_d_claim_state;

select /*+ FULL ( dcs ) */ count(*) from scm_d_claim_state dcs;

select /*+ INDEX ( dcs scm_d_claim_state_pk ) */ count(*) from scm_d_claim_state dcs;


We are in the process of upgrading our datawarehouses from 9i to 10g.

The most useful features we envisaged were improvements to the merge command and dml error logging. These are areas where we are encountering problems. Confidence in 10g is dropping.

I have been involved in upgrades from version 6 onwards, and generally they have all been very positive in terms of ease, functional richness and performance improvements.

Steve


How Cool Is This -- Practice?

Duke Ganote, August 29, 2007 - 11:27 am UTC

We finally upgraded from 9iR2 to 10.2.0.3 on our main production server.  After reading your blog on DML Error Logging
http://tkyte.blogspot.com/2005/07/how-cool-is-this.html
I put together these 2 examples for our developers for DML error logging.  The first is a 'ZERO DEFECT' process (REJECT LIMIT 0); the second, a 'WHATEVER' process (REJECT LIMIT UNLIMITED).  I attempted to utilitze the error tagging and other instrumentation (tho I didn't show use of our bespoke ETL log tables).

create table t_emp /*  :))  */
    ( empno number primary key, job varchar2(7) )
/
exec dbms_errlog.create_error_log('T_EMP')
/
DECLARE
  l_err_tag$   VARCHAR2(40) :=  TO_CHAR(systimestamp);
  l_insert_cnt NUMBER;
  l_error_cnt  NUMBER;
BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(l_err_tag$);
  DBMS_APPLICATION_INFO.SET_MODULE
  ( module_name => 'LOGGING INSERT INTO t_emp'
  , action_name => 'in process');
  BEGIN <<up_to_error_quota>>
    INSERT INTO t_emp
    SELECT empno, job FROM scott.emp
    LOG ERRORS INTO err$_t_emp
    ( l_err_tag$ )
    REJECT LIMIT 0 -- STOP IF ANY ERRORS
    ;
  EXCEPTION WHEN OTHERS THEN NULL; -- Tom's favorite
  END up_to_error_quota;
  l_insert_cnt := SQL%ROWCOUNT;
  SELECT COUNT(*)
    INTO l_error_cnt
    FROM err$_t_emp
   WHERE ora_err_tag$ = l_err_tag$
  ;
  DBMS_APPLICATION_INFO.set_action
  ( action_name => l_insert_cnt||' inserted since '||l_error_cnt||' bad.');
END;
/

SQL>  select module, action from v$session where username = user;

MODULE                                           ACTION
------------------------------------------------ ----------------------
LOGGING INSERT INTO t_emp                        0 inserted since 1 bad.

SQL> select ora_err_mesg$, count(*) from err$_t_emp where ora_err_tag$ = userenv
('client_info') group by ora_err_mesg$;

ORA_ERR_MESG$
------------------------------------------------------------
            COUNT(*)
--------------------
ORA-12899: value too large for column "DGANOTE"."T_EMP"."JOB" (actual: 8, maximum: 7)
                   1

SQL> select count(*) from t_emp;

            COUNT(*)
--------------------
                   0

DECLARE
  l_err_tag$   VARCHAR2(40) :=  TO_CHAR(systimestamp);
  l_insert_cnt NUMBER;
  l_error_cnt  NUMBER;
BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(l_err_tag$);
  DBMS_APPLICATION_INFO.SET_MODULE
  ( module_name => 'LOGGING INSERT INTO t_emp'
  , action_name => 'in process');
  BEGIN <<up_to_error_quota>>
    INSERT INTO t_emp
    SELECT empno, job FROM scott.emp
    LOG ERRORS INTO err$_t_emp
    ( l_err_tag$ )
    REJECT LIMIT UNLIMITED -- plow (or plough) onward!
    ;
  EXCEPTION WHEN OTHERS THEN NULL; -- :))
  END up_to_error_quota;
  l_insert_cnt := SQL%ROWCOUNT;
  SELECT COUNT(*)
    INTO l_error_cnt
    FROM err$_t_emp
   WHERE ora_err_tag$ = l_err_tag$
  ;
  DBMS_APPLICATION_INFO.set_action
  ( action_name => l_insert_cnt||' inserted, '||l_error_cnt||' bad.');
END;
/

SQL>  select module, action from v$session where username = user;

MODULE                                           ACTION
------------------------------------------------ -------------------------------
LOGGING INSERT INTO t_emp                        9 inserted, 5 bad.

SQL> select ora_err_mesg$, count(*) from err$_t_emp where ora_err_tag$ = userenv
('client_info') group by ora_err_mesg$;

ORA_ERR_MESG$
--------------------------------------------------------------------------------
            COUNT(*)
--------------------
ORA-12899: value too large for column "DGANOTE"."T_EMP"."JOB" (actual: 9, maximum: 7)
                   1

ORA-12899: value too large for column "DGANOTE"."T_EMP"."JOB" (actual: 8, maximum: 7)
                   4


DML error logging "ETL thru a view"

Duke Ganote, October 05, 2007 - 12:38 pm UTC

I've previously advocated encapsulating ETL logic, not in SQL-in-a-PL/SQL-cursor, but a view.  The view can be used for a cursor as well as as elsewhere.  
http://tinyurl.com/ytj8y7
Looks like DML error logging makes that very do-able:

-- set up script
create table t ( a char(1) );
insert into t values ( '1');
insert into t values ( 'A'); -- INEVITABLE OLTP DATA GLITCH!!!
create view t_vw as select to_number(a) a from t;
create table t_tgt as select * from t_vw where rownum = 0;
exec dbms_errlog.create_error_log('T_TGT');

SQL> select * from t_vw; -- VIEW 'BLOWS UP' !!!
ERROR:
ORA-01722: invalid number

no rows selected

SQL> insert into t_tgt select * from t_vw -- ERROR LOGGING OK !!!
  2  log errors into err$_t_tgt reject limit unlimited;

1 row created.

SQL> select * from t_tgt;

         A
----------
         1

SQL> select * from err$_t_tgt;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
----------------------------------------------------
ORA_ERR_ROWID$
----------------------------------------------------
OR
--
ORA_ERR_TAG$
----------------------------------------------------
A
----------------------------------------------------
           1722
ORA-01722: invalid number

I

A

DML Error Logging why not log all errors

A reader, March 30, 2011 - 2:04 am UTC

Hi Tom,
Suppose a row has more than one constraint violation,
Say two columns are violating Not null constraint and one column violates check constraint.

Why does DML error logging not record all the constraint violation ?
It only records the first one which occurs.

I would like to report all possible wrong things in a row not just first one.

How to make this happen ?

Tom Kyte
March 30, 2011 - 3:08 am UTC

you cannot, once a constraint is found to be violated, the process stops - the record cannot be inserted, it is logged as an error. We don't process everything - once an error happens - we are done.

ETL and error logging

A reader, March 31, 2011 - 7:43 am UTC


Tomkyte mantra

Rajeshwaran, Jeyabal, January 19, 2018 - 6:53 am UTC

Sorry to ask again.

Tomkyte's Mantra is available at this link.

http://tkyte.blogspot.com/2006/10/slow-by-slow.html

could you help that loaded here in AskTom?
Chris Saxon
January 19, 2018 - 10:55 am UTC

https://asktom.oracle.com/Misc/slow-by-slow.html

If you're looking for a page on Tom's blog, take the final *.html and replace the domain etc. with:

https://asktom.oracle.com/Misc

12c - CBAC

Rajeshwaran, Jeyabal, February 01, 2018 - 8:27 am UTC

Team,

did the above trick, but that doesn't work for this link.
http://tkyte.blogspot.com/2013/07/12c-code-based-access-control-cbac-part.html
replaced the domain
https://asktom.oracle.com/Misc/12c-code-based-access-control-cbac-part.html
but doesn't work, could you help us?
Connor McDonald
February 01, 2018 - 11:11 am UTC

I'll see if I can dig up the original and convert it.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library