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
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
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
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.
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
"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
you're blog helped me :)