Weird situation with Oracle's "Log Errors" capability

One of the coolest features introduced in Oracle 10gR2 is the ability to log errors in large DML commands such as

"INSERT INTO table_name SELECT ....".

There's a really awesome writeup on this at this link so there's no need for me to rewrite what it is and what it does (read the article).

One silly scenario I just came across while implementing the following.
I wanted to create an error log on a table who's name is 30 characters long

CTIM_DATA.DEV01> BEGIN
2 DBMS_ERRLOG.CREATE_ERROR_LOG('MYREALLY_LONG_TABLE_NAME_HERE');
3 END;
4 /

PL/SQL procedure successfully completed.


This command created an error log table named "ERR$_MYREALLY_LONG_TABLE_NAME" (note that the "_HERE" has been removed).

I then tried to compile a stored procedure that had a command like the following

"INSERT INTO MYREALLY_LONG_TABLE_NAME_HERE (cola, colb)
SELECT a, b FROM ....
LOG ERRORS
REJECT LIMIT UNLIMITED"


No luck, I get a "PL/SQL: ORA-00972: identifier is too long" error when trying to compile.

Looking through metalink and Google didn't help much, so I then tried supplying the name of the error log table to the DBMS_ERRLOG.CREATE_ERROR_LOG procedure, once I did that I had to modify the "INSERT INTO" command to tell it where to log the errors.

Note the "INTO [schema.]table" section below.

LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]


In a nutshell, I guess Oracle is smart enough on the creation of the table to truncate the error log table at 30 characters but it's not smart enough yet when actually using the "LOG ERRORS" feature.

I'm hoping Oracle fixes this (someday) and until then I'm going consider using the "INTO" clause as a best practice when using the "LOG ERRORS" feature.

Until next time...Rich

Comments

joxo said…
Thanks for this post. It just saved me pain!
Alkesh said…
thats a lot Rich for this. I was really stuck on this part and did not know what went wrong.
you're blog helped me :)

Popular Posts