Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 148,965
  • Archives

  • Categories

  • Subscribe

  • Advertisements

‘Weird’ Error with Oracle’s “Log Errors” potentiality: — PL/SQL: ORA-00972: identifier is too long

Posted by FatDBA on November 20, 2013

Going to discuss one of the uncanny situation faced by me couple of minutes ago on one of my Test Machine. (Yes!, work on Vacations as well 🙂 )

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 ….”.

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

PRASHANT.DIXIT_DEV07> BEGIN
2        DBMS_ERRLOG.CREATE_ERROR_LOG(‘MYREALLY_LONG_TABLE_NAME_HERE’);
END;
/

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 haven’t checked the same in 11g and until then I’m going consider using the “INTO” clause as a best practice when using the “LOG ERRORS” feature.

Hope That Helps
Prashant Dixit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: