Tuesday, July 05, 2005

How cool is this

Taking a look at a new 10g Release 2 feature, DML Error Logging.  Also, a stupid thing I saw in the news.

First for the most ridiculous news item of the day.  Astrologer Sues NASA Over Comet Mission.  Hello?  Anyone at home?  What a waste.

Ok, back to the real world.  I’ve found that the most dreaded words someone writing for a magazine can see are “your column is short, do you have 400 words to add”.  Well, that can be as bad as “your column is long, chop 400 words”.  I got the “too short” over the weekend and had to find another thing or two to write about for my next column.  I vaguely recalled that I knew this feature was coming but I forgot to look for it.  In my search for an extra 400 words, I stumbled upon it again.

DML Error Logging, instead of the 100,000 row update/insert/whatever failing because a single row doesn’t quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table!  I mean, this isn’t just cool, this is “change my world, rewrite the books, stop the presses, wow, knock me over with a feather”.

And – the best part – it is really quite easy and intuitive.  This is cool.  Here is a quick demonstration.  We’ll just take the SCOTT.EMP table:

ops$tkyte-ORA10GR2> create table emp
  2  as
  3  select * from scott.emp where 1=0;
 
Table created.

 
and using a new supplied package – we’ll generate the error log for it.  You could create this by hand, you could specify the name via this API, you could do lots of stuff, this is the shortest way.  It’ll default everything and just name the error log table ERR$_<tablename> which seems sensible:

ops$tkyte-ORA10GR2> exec dbms_errlog.create_error_log( 'EMP' ); 
PL/SQL procedure successfully completed.

 
ops$tkyte-ORA10GR2> desc err$_emp
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ORA_ERR_NUMBER$                  NUMBER
 ORA_ERR_MESG$                    VARCHAR2(2000)
 ORA_ERR_ROWID$                   ROWID
 ORA_ERR_OPTYP$                   VARCHAR2(2)
 ORA_ERR_TAG$                     VARCHAR2(2000)
 EMPNO                            VARCHAR2(4000)
 ENAME                            VARCHAR2(4000)
 JOB                              VARCHAR2(4000)
 MGR                              VARCHAR2(4000)
 HIREDATE                         VARCHAR2(4000)
 SAL                              VARCHAR2(4000)
 COMM                             VARCHAR2(4000)
 DEPTNO                           VARCHAR2(4000)
 
There is the error logging table, they picked datatypes that would be “safe” for each column — if you inserted a JOB that was too long, no worries, it has to fit into a VARCHAR2(4000).  EMPNO isn’t really a number in your input? No problem, it’ll fit into a VARCHAR2 as well.  The other columns you see in here are useful too — the ORA-XXXX error number, the text of the error, the ROWID of the offending row(s) from an update or delete, the operating type (I/U/D) and optionally a ‘tag’ you provide as part of your SQL operation.  That tag will be useful to determine what step of your ETL (extract/transform/load) process failed. 

So, let’s try this out, we’ll add some constraints:

ops$tkyte-ORA10GR2> alter table emp add constraint emp_check_sal check(sal > 900);
Table altered.
 
ops$tkyte-ORA10GR2> create trigger emp_trigger
  2  after insert on emp for each row
  3  begin
  4      if ( :new.ename = 'ALLEN' )
  5      then
  6        raise_application_error
  7        (-20024, 'Failed Validation' );
  8      end if;
  9  end;
 10  /
Trigger created.
 
ops$tkyte-ORA10GR2> alter table emp add
  2  constraint emp_pk primary key(empno);
Table altered.

and using just the old fashioned INSERT, we can see what normally would happen:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp;
insert /*+ APPEND */ into emp
       select * from scott.emp
*
ERROR at line 1:
ORA-02290: check constraint 
           (OPS$TKYTE.EMP_CHECK_SAL) violated
 
ops$tkyte-ORA10GR2> select * from emp;
no rows selected

 

A big “nothing” is what happens, the SQL either entirely happens or doesn’t happen at all.  But, add “LOG ERRORS” and check it out:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp
  3  LOG ERRORS REJECT LIMIT UNLIMITED;
12 rows created.
 
Now, since anyone that can spell Oracle knows there are 14 rows in emp – something happened here.  Something good in a way.  We had errors (bad) that were not ignored (good) but logged off to the side (better) and we still took advantage of BULK OPERATIONS (best of all).  We can review the bad records:

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3         EMPNO,
  4         ORA_ERR_MESG$
  5    from err$_emp;

ORA_ERR_NUMBER$ OR EMPNO ORA_ERR_MESG$
--------------- -- ----- ---------------
           2290 I  7369  ORA-02290: chec
                         k constraint (O
                         PS$TKYTE.EMP_CH
                         ECK_SAL) violat
                         ed
 
          20024 I  7499  ORA-20024: Fail
                         ed Validation
                         ORA-06512: at "
                         OPS$TKYTE.EMP_T
                         RIGGER", line 4
 
                         ORA-04088: erro
                         r during execut
                         ion of trigger
                         'OPS$TKYTE.EMP_
                         TRIGGER'

It even works during UPDATES:

ops$tkyte-ORA10GR2> update emp
  2     set sal = sal - 2000
  3   where sal between 2000 and 3000
  4  LOG ERRORS ('My Update')
  5  REJECT LIMIT UNLIMITED;
 
3 rows updated.

 
But really, we didn’t update three rows.  Note this time I passed in a nice little name, which we can now use against this cumulative error log table (the failed inserts are still in there as well)

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3             ORA_ERR_TAG$,
  4             ORA_ERR_ROWID$,
  5         EMPNO,
  6         ORA_ERR_MESG$
  7    from err$_emp
  8   where ora_err_tag$ is not null;
 
  ORA OR ORA_ERR_T ORA_ERR_R EMPNO ORA_ERR_MESG$
----- -- --------- --------- ----- ---------------
 2290 U  My Update AAAM04AAE 7698  ORA-02290: chec
                   AAAAGdAAD       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed
 
 2290 U  My Update AAAM04AAE 7782  ORA-02290: chec
                   AAAAGdAAE       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed

We have the row data (EMPNO is filled in) as well as the rowid’s of the bad rows.  We can see what caused the error — the check constraint violation, and see how many rows violated the check constraint — two in this case.  Our update would have modified 3 rows had all of the rows been “OK”, but it really only did 1.

This new feature changes everything.  No more excuses in the future, BULK operations are in style, they are cool, they are efficient and you’ll be able to erase a ton of code (which believe it or not is a cool thing, erasing code). 

Can you tell I’m sort of excited about this new feature?

POST A COMMENT

57 Comments:

Blogger Alberto Dell'Era said....

Very very interesting - but

a) does it add overhead (for a no-error operation) - say, was the direct-load insert turned silently into a normal insert (playing the devil's advocate here) ?

and most importantly

b) can we retain the atomicity of the operation (having the whole statement rollback as normal) and still have the offending rows logged into the error table ?

Tue Jul 05, 03:53:00 PM EDT  

Anonymous Anonymous said....

I'd agree. Very useful, and potentially able to obsolete lots of code.

but (and I haven't looked yet for this, so if it is there I apologize),

wouldn't it be better if after the insert it would say "12 rows inserted, 2 errors logged". And it provided that feedback in a pl/sql variable (i.e. sql%rowcount) as maybe "sqlerrlog%rowcount"? Then my code does not have go look for rows in the $err table.

The concern here is that by not explicitly telling the user about errors, they could get overlooked.

Mark

Tue Jul 05, 03:53:00 PM EDT  

Blogger Tim... said....

Wow! That is bigtime cool!

I can't tell you how may times I've had to use bulk binds with collections and SAVE EXCEPTIONS to make sure an operation completes, where straight DML would have done the job.

Now all I have to do is invent some plausible reason to upgrade from Release 1 to Release 2 :)

Tue Jul 05, 03:58:00 PM EDT  

Anonymous Anonymous said....

Isn't the time this new feature could save a "plausible" enough reason to upgrade in your case?

This new feature sounds ridiculously awesome; however, I am curious about Alberto's second question and agree completely with the "2 errors logged" and some sort of sql%errorcount variable in PLSQL.

Tue Jul 05, 04:11:00 PM EDT  

Blogger Tim... said....

You're preaching to the converted here!

I'm just thinking of the fear factor when I mention the "U" word :)

Tue Jul 05, 04:15:00 PM EDT  

Anonymous Mark from NY said....

That's great! I've been doing this by hand using cursor for loops, the exception block logs errors to a temporary table using the "when others" clause. Not very neat. Looking forward to benchmarking the two methods. :)

Tue Jul 05, 04:21:00 PM EDT  

Blogger Niall said....

It is indeed neat. Of course now I'm regretting trashing the r2 beta documentation in search of disk space. I want to know the gotchas. triggers, RI etc all spring to mind.

Tue Jul 05, 04:30:00 PM EDT  

Blogger Carl said....

Hi Tom, ther very coolest thing for me is that it's easier to post comments to your blog; Not as your asktom-site - i almost never had a chance to comment something - Great!!
Thank you
Carl

Tue Jul 05, 04:36:00 PM EDT  

Blogger Alberto Dell'Era said....

My second q was aimed at debugging a third-party or legacy application, where you don't want to alter the "default" behaviour in any way (for obvious reasons) but you definetely want to know what "row" caused the statement to fail.

It could be excellent for your applications too, of course.

Tue Jul 05, 04:46:00 PM EDT  

Blogger Arun Mathur said....

Great read, Tom. In a way, it reminds me of SQL*Loader embedded in PL/SQL, where the exceptions table is the parallel to a .bad file, except this feature definitely looks to have more to offer. Looking forward to playing around with this!

Regards,
Arun

Tue Jul 05, 05:09:00 PM EDT  

Anonymous Anonymous said....

So you still go to yahoo news then?

Tue Jul 05, 05:53:00 PM EDT  

Blogger Thomas Kyte said....

Excellent comments so far. I have to benchmark this out - there will be caveats (based on a CURSORY overview). But this is the reason I won't write about 10gr2 in the book I'm working on now. Everything would be speculative - I don't mean to make this look like a silver bullet or anything. I'll be more verbose in regards about my last sentence: I'm excited about this new, untried, not yet field tested thing :)

But I can answer some of the items (and the blog tomorrow will be the benchmark results if I get a chance to set up some simple tests, won't be definitive but will give us a good idea, there do appear to be some differences in some cases and I want to try and isolate them)

And, it'll be relevant to benchmark this against

a) not this -- just the DML
b) the slow by slow approach

as well. even if it just TIES the slow by slow, it is worth it.



Alberto - "can we retain the atomicity of the operation"

No (but I would not phrase the question that way :), you set up the reject limit (0 is the default) and if you hit it (the limit) the statement will roll back but if you don't, it won't roll back.

The concern here is that by not explicitly telling the user about errors, they could get overlooked.

Mark


yeah, BUT you have to ask for this, that is what the TAG is all about. You have to ASK to have log errors on, so you KNOW to look see if there where any.

My second q was aimed at debugging a third-party or legacy application,

but in line with what I just said to Mark, you have to ASK for this, it doesn't just HAPPEN. You need modified code.



but check back tomorrow -- there are many cases to inspect, (no constraints, check constraints, not null constraints, triggers, indexes, no indexes, primary keys, foreign keys) * (generate redo, no generate redo X direct path )............


and I want the near production code, not the beta stuff I have at home :)

Tue Jul 05, 07:06:00 PM EDT  

Blogger Thomas Kyte said....

So you still go to yahoo news then?

google is my first place, yahoo has the "oddly enough" news which I like *a lot*, it (astrologist) was linked to from there.

Tue Jul 05, 07:08:00 PM EDT  

Anonymous Gabe said....

If dbms_error has no option to create the err$_% with a timestamp then I'll call this 'feature' "the ugly". A table representing an event without some date/time component in sight ?!?! ... hmmm.

BTW (since you have the s/w) ... what happens when there are multiple errors on a row? Say, hiredate is not a valid date and sal is not really a number?

Tue Jul 05, 07:13:00 PM EDT  

Blogger Thomas Kyte said....

gabe said

the table can be dynamic (a table /statement if you like) but consider the table like a "bad" file. And remember -- the TAG, you have THE TAG and the tag is any string you like.


first exception hit on a row is what gets logged. (for the multiple errors on a row question)

Tue Jul 05, 07:50:00 PM EDT  

Anonymous Gabe said....

If I want a date/time component in the tag between 2 runs of my ETL ... wouldn't the SQL have to be dynamic? Or can one use a bind for the tag?

Still think a PK from a sequence and a timestamp column in the ERR$_% table would've been nice.

The "bad" file would get overwritten between runs (that is true) ... but it has a date/time and there is an order (sequential) to its rows.

But better wait and see/test it for myself rather than assume. Certainly worth investigating.

Tue Jul 05, 09:18:00 PM EDT  

Anonymous Partha said....

When such a feature comes, then you think of the obvious, 'Why was this not there before?' Does it take so many years and versions to bring out this 'basic' thing ?. The same thing happened with dropping a column from the table.

When things become simpler, it sometimes makes you wonder why was it complicated in the first place. Why did we have to do use a workaround for dropping columns, why did we have to do slow-by-slow processing for logging errors, why did we have to define tablespaces, datafiles etc when it can do it automatically? why do we have to keep tuning memory when it can tune it automagically as it knows the memory usage, why did we have to explicity do bulk operations when it can do it automagically?, why didn't the magic new dual table with less consistent gets exist prior to 10g?

Tue Jul 05, 09:25:00 PM EDT  

Anonymous Anonymous said....

I would guess that such things as self management and built-ins of common user uses can only come after the basic building blocks have been finely tuned and tested. Once things run well with user input, then you can automate and simplify common user activities.

Tue Jul 05, 10:28:00 PM EDT  

Blogger Chris said....

Sweet!

Tom -

First, thank you for making available the opportunity to learn from your experience. It has made a world of difference in a short time for me.

What a jewel you've found! What is the best way to handle this type of thing outside of 10g? I'm working with 9ir2 and have yet to come across a nice way of dealing with this.

When do you think your next book will be published? I'm still working through the concepts guide and your other books, but there's always room for more!

Also, have you had time to play with Google Earth yet? If so, what do you think? Feel free to respond at your leisure.

Chris

http://cjdanos.blogspot.com

Tue Jul 05, 11:06:00 PM EDT  

Blogger Noons said....

"Can you tell I’m sort of excited about this new feature?"

And why not, indeed? FINALLY, exception handling reaches DML!

Of course, performance et all needs looking into. But is there any particular reason why that wouldn't be fixable? Don't think so.

This alone might finally move the powers that be to upgrade from dinossaur releases...

Tue Jul 05, 11:55:00 PM EDT  

Blogger Kalita said....

I wish it was released with 9iR2. I had to develop something similar for external data coming to our system. Used the same ideas i.e. VARCHAR2 columns,storing the error information etc.

Wed Jul 06, 12:23:00 AM EDT  

Anonymous Anonymous said....

Tom,
I've been reading about 10g R2 features for quite awhile now. I'm currently on 10.1.0.3 and have 4 tars with Support where they're telling me to upgrade to 10.1.0.4 because of various bugs. My response has been "I've heard that that 10g R2 will be released any day now and I'll upgrade to R2 after it's released" (after testing of course). Bottom line -- why can't Oracle Corp announce an official relaease date instead of all these teases about what's possible with 10g R2.

Wed Jul 06, 12:24:00 AM EDT  

Blogger Kalita said....

And how about this for news
http://www.msnbc.msn.com/id/8417691/

:-)

Wed Jul 06, 12:47:00 AM EDT  

Blogger fuzzyllamaduck said....

DML Error Logging, great stuff thanks for the heads-up Tom. While not of the same caliber, the DBMS_OUTPUT modifications are also a welcome change. Adieu ORU-10027 and 28--could it be "p" incorporated under the hood.

For the blog's youngsters http://www.albinoblacksheep.com/flash/llama.php

Wed Jul 06, 01:44:00 AM EDT  

Anonymous Bart said....

Now, since anyone that can spell Oracle knows there are 14 rows in emp


I didn't :)

Wed Jul 06, 01:56:00 AM EDT  

Anonymous V.K. said....

Great feature, indeed. But I'd like to remind that similar "cool feature" (for constraints only, to be fair) was supported by Informix RDBMS for ages (since 1996 and version 7, at least) with:

START VIOLATIONS TABLE FOR ...

and then:

SET CONSTRAINTS ... FILTERING [WITH ERRORS]

After that simple setup, "bad" rows, with all correct column datatypes, go to a separate "violations" table, information about each violated constraint goes to the "diagnostics" table. You may even ask for special error message in case of any rows filtered out (WITH ERRORS clause).

Looks like some kind of "reinventing the wheel", surely better then previos designs.

Wed Jul 06, 02:16:00 AM EDT  

Anonymous Pratap said....

Hmmm.... now no need for this trick in 10g -

http://asktom.oracle.com/pls/ask/f?p=4950:8:11761908411154861390::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4070171725287

Wed Jul 06, 04:11:00 AM EDT  

Anonymous Pratap said....

Sorry cannot paste the entire link here, so here is the subject of the asktom thread - "Error handling using implicit cursors for insert"

By the way when I tried to access the same link gave this error -

ORA-01400: cannot insert NULL into (
"ASK_TOM"."WWC_ASK_QUESTION_ACCESS_
LOG$".
"DISPLAYID")

(If a line is big without a space, like a url or the error above, then it gets truncated, it does not get wrapped in this blog comment - any workaround?. This time I simply put some new line characters in the above error message)

Wed Jul 06, 04:19:00 AM EDT  

Blogger Alberto Dell'Era said....

My second q was aimed at debugging a third-party or legacy application,

but in line with what I just said to Mark, you have to ASK for this, it doesn't just HAPPEN. You need modified code.

Absolutely, I know that; I was saying that, if it were possible to activate ("ASK for") this feature to get the evil rows logged, without modifing the behaviour of the system in any other way ... well, it would be a formidable aid for debugging third-party applications, the nightmare of any dba or system integrator.

Wed Jul 06, 04:34:00 AM EDT  

Anonymous Will said....

DML error logging looks like a useful weapon for those of us who have to debug DML from verbose J2EE mid-tier log files. I routinely see hundreds of lines of detailed information about which class does what, with only the occasional reference to ORA-nnnn to help.

Wed Jul 06, 04:44:00 AM EDT  

Blogger melanie caffrey said....

In a word, fantastic!



Tim said ...

I can't tell you how may times I've had to use bulk binds with collections and SAVE EXCEPTIONS to make sure an operation completes, where straight DML would have done the job.



Hear, hear. Since 9iR2, %BULK_EXCEPTIONS has been my primary method for handling this.

What a great new feature!!

Wed Jul 06, 07:15:00 AM EDT  

Blogger Connor McDonald said....

I would contend (sadly) that if you got a bunch of typical Oracle developers in a room and said "Look at this cool feature... it might save you lots of bulk-binding code and save-exceptions clauses"... there would be lengthy silence, followed by...

"What's bulk?"

:-(

Wed Jul 06, 08:56:00 AM EDT  

Anonymous Philip said....

Tom, everyone knows there are 400,000,437 rows in Orcle's EMP table... :P

You just haven't accounted for VPD on the EMP table; only Ellison can see the remaining 400,000,423 rows - why is he hiding this from us? Someone needs to file a "freedom of information act" brief to see these rows immediately - the American people deserve to see these rows!

Oh, well - just kidding - I was bored - have a good one !

Wed Jul 06, 09:01:00 AM EDT  

Blogger Niall said....

Connor said

I would contend (sadly) that if you got a bunch of typical Oracle developers in a room and said "Look at this cool feature... it might save you lots of bulk-binding code and save-exceptions clauses"... there would be lengthy silence, followed by...

"What's bulk?"

:-(


God, you are optimistic. I'd get "What's an exception?"

Wed Jul 06, 05:17:00 PM EDT  

Blogger Kalita said....

Niall and Connor,
Come on, we developers are not that bad! :-)

Thu Jul 07, 12:16:00 AM EDT  

Anonymous Anonymous said....

Hi Tom, why do you say
"First for the most ridiculous news item of the day. Astrologer Sues NASA Over Comet Mission. Hello? Anyone at home? What a waste."

Thu Jul 07, 03:25:00 PM EDT  

Blogger Thomas Kyte said....

Anonymous said...
Hi Tom, why do you say


Umm, did you read the article?

If so, what is your opinion of suing NASA over exploding a probe into a comet is.

Mine is "what a waste", what an utter and complete waste.

Thu Jul 07, 03:58:00 PM EDT  

Anonymous Anonymous said....

I got Tom, I was understading
you was against the experiment.
Now I understad it was about the suing

Thu Jul 07, 04:05:00 PM EDT  

Anonymous Andrew said....

Does this feature require that the table be defined exactly as dbms_errorlog creates it? are we free to add a date column then write an insert trigger?
If so, timestamping and notifications are easy.

Fri Jul 08, 12:34:00 PM EDT  

Anonymous Richard Armstrong-Finnerty said....

PLEASE NOTE: I AM NOT AN ASTROLOGER!!!

So, an Astrologer attempts to sue NASA for damaging a comet. Fair enough, say I, though I do not share the Astrologer's faith/belief system, because, as a Capricorn, I am drawn more to pragmatism ;)

I am sure that Astrologers are offended by what was done, as it was a quite violent way of exposing sub-surface material.

NASA is, ultimately, a group of human beings; as are Astrologers. Each side has its adherents and its detractors. To say which side is *right*, in this case, is a hard thing to do because we are each entitled to our beliefs: NASA sees a big, dirty snowball, whilst Astrologers see something spiritual. It'll be very interesting to see who wins - remember the ending of Miracle on 34th Street!

Sat Jul 09, 06:38:00 AM EDT  

Blogger Thomas Kyte said....

Richard Armstrong-Finnerty said...

Hey, maybe NASA was just trying to change it's luck.


seems to thinks so ;)

Sun Jul 10, 05:27:00 PM EDT  

Anonymous Anonymous said....

Hi,

I have a question about other 10gR2 feature.
Did you see Oracle Backup Media Manager in install package ?

I want to test it but it exist only in release note ;)

regards,
Marcin

Wed Jul 13, 09:04:00 AM EDT  

Anonymous Octavio Kishi said....

Will this works with SQL Loader? So, we dont have a .bad file, the records will be inserted in the err_table? By the way, it's excelent do us (developers!).

Wed Jul 13, 10:49:00 PM EDT  

Blogger Thomas Kyte said....

Octavio Kishi said...

Will this works with SQL Loader?


No, sqlldr has the bad file already.

This is for external tables so you don't even have to deal with sqlldr anymore.

Thu Jul 14, 08:17:00 AM EDT  

Anonymous Deepak said....

The ERR Logging doesn't work with Insert APPEND 'and' PK Violations. Is that right?

Wed Mar 25, 02:01:00 AM EDT  

Blogger Thomas Kyte said....

@Deepak

documentation says.....

(read it)

Wed Mar 25, 07:15:00 AM EDT  

Blogger Srikanth said....

For table names greater than 25 characters, it truncates at the end. We have to query data dictionary tables to get the error log table.

Wed May 20, 05:07:00 PM EDT  

Blogger Thomas Kyte said....

@Srikanth

two things

a) if you know how it works (truncate at 25) why do you have to query anything??? think about it, would not the name be PREDICABLE - could not you just use substr???

b) you do know that you can name it whatever you feel like, you do not have to accept the defaults right?

Thu May 21, 11:31:00 AM EDT  

Blogger Anupam Pandey said....

Hi Tom,
I tried this on my environment but it does not seem to be working there as per your demonstration.

Please tell me if I am doing anything wrong ..

SQL> create table test_object
2 as
3 select *
4 from user_objects
5 where object_id is not null ;

Table created

SQL>
SQL>
SQL> BEGIN
2 DBMS_ERRLOG.CREATE_ERROR_LOG(
3 dml_table_name => 'test_object',
4 err_log_table_name => 'test_object_error'
5 );
6 END;
7 /

PL/SQL procedure successfully completed

SQL>
SQL> ALTER TABLE test_object
2 add CONSTRAINT idx_id primary key (object_id);

Table altered

SQL>
SQL> insert /*+append */into test_object
2 select *
3 from test_object
4 where rownum < 2
5 LOG ERRORS INTO test_object_error ('INSERT..SELECT..RL=UNLIMITED')
6 REJECT LIMIT UNLIMITED;

insert /*+append */into test_object
select *
from test_object
where rownum < 2
LOG ERRORS INTO test_object_error ('INSERT..SELECT..RL=UNLIMITED')
REJECT LIMIT UNLIMITED

ORA-00001: unique constraint (DNA_V08.IDX_ID) violated

SQL>

Tue Aug 31, 01:08:00 AM EDT  

Blogger Thomas Kyte said....

@anupam

Read about the documented limits of this... unique constraints with direct path (you used append which is direct path) is not supported

Tue Aug 31, 08:18:00 AM EDT  

Anonymous Anonymous said....

Tom,
In the example you have provided, direct path is used too so why the error seen by Anupam Pandey? Am I missing something?

Wed Aug 01, 10:42:00 PM EDT  

Blogger Thomas Kyte said....

@anonymous my example did not have a unique constraint

Thu Aug 02, 07:21:00 AM EDT  

Blogger Piyush Agarwal said....

Hi Tom,
As you said, the error log table defaults to ERR$_ and the tablename is the first 25 chars of the original table name. Now if I wish to create custom error log on each table having the following table names like -
T1234567890123456789012345678a
T1234567890123456789012345678b
T1234567890123456789012345678c
and all of them have similar data structure.

Now 1st err log will be created as
ERR$_T123456789012345678901234.
For 2nd and 3rd, as per blog discussion, it would internally generate the name.

Which Data dictionary table will show me the related table ? How would I know that a table xyz is actually for T1234567890123456789012345678b and not for T1234567890123456789012345678c.

Thanks n Regards,
Piyush

Fri Nov 02, 12:43:00 PM EDT  

Blogger Thomas Kyte said....

@Piyush,

they are not related in any sense, they are just tables. You would be responsible (by using the tag probably) for knowing which table you specified during the DML operation.

the fact is - the one error log table could have data from all three, none, two of them and so on. In fact all three could have data from all three tables.

It is like a sequence, they are not attached to "a table".

Mon Nov 05, 06:05:00 AM EST  

Blogger Gary Myers said....

"It is like a sequence, they are not attached to "a table"."

You're going to have to stop saying that once 12c is out :)

Wed Nov 07, 03:58:00 AM EST  

Blogger Thomas Kyte said....

@Gary

technically it will still be true, it is not like you see the sequence for an identity... ;)

Wed Nov 07, 06:18:00 AM EST  

Blogger Piyush Agarwal said....

Thanks to all for the answer. It looks like 12c would be a very interesting release and yes it seems to be working like a sequence in 11g.

Sat Nov 10, 03:53:00 AM EST  

POST A COMMENT

<< Home