Ask Tom "problem with long table name"

It's good stuff From Ask Tom "problem with long table name"

Tom,
We had problem with the long table name:
SQL> sho user
USER is "REF"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
ACCOUNT_ADDR_USAGE_TYPES
ACCOUNT_ASSOC_TYPES
ACCOUNT_STATUSES
ACCOUNT_TRANSACTION_STATUSES
ACCOUNT_TRANSACTION_TYPES
ACCOUNT_TYPES
APPLICATIONS
BUSINESS_CODES
CATEGORY_DEPARTMENTS
CERTIFICATION_STATUS
CLIENT_SEGMENTS
COMPANIES
CONTACT_POINT_USAGE_TYPES
CURRENCIES
DEPARTMENTS
FUNCTIONS
GEOGRAPHIC_REGIONS
GEOGRAPHIC_REGION_TYPES
GEO_REGION_ROLLUPS
GEO_REGION_ROLLUP_TYPES
JOBS
LANGUAGES
LEGACY_OFFICE_DEPARTMENTS
MARKETING_SEGMENTS
OFFICES
OFFICE_DEPARTMENTS
PARTY_ACCOUNT_ROLES
PARTY_ASSOC_TYPES
PARTY_ASSOC_TYPE_GROUPS
PARTY_IDENTIFICATION_TYPES
PARTY_IMPORTANCE_LEVELS
PARTY_TYPES
TABLE_NAME
------------------------------
PAYMENT_CALC_METHOD
PAY_TERM_TYPES
PHONE_TYPES
PROCESSES
ROLES
ROLE_FUNCTIONS
STANDARD_HINT_QUESTIONS
TRIGGER_ERRORS
40 rows selected.
SQL> drop table ACCOUNT_TRANSACTION_STATUSES;
drop table ACCOUNT_TRANSACTION_STATUSES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01401: inserted value too large for column
ORA-06512: at line 22
SQL> drop table ref.jobs;
Table dropped.
We only may drop the table with shot name or use drop user username
cascade to drop the table with long name.
Is this a bug? What is the workaround for it.
Thanks for your help.
Pauline
and we said...Is this a bug -- you betcha.
Its a bug in YOUR DDL trigger!!!
You have a trigger, it has at least 22 lines of code and on line 22 you are inserting the
ora_dict_obj_name into an audit table. Audit table is too small to hold it. It fails.
We fail.
Solution... Fix trigger or fix audit table.
Example:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table ACCOUNT_TRANSACTION_STATUSES ( x int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table ACCOUNT_TRANSACTION_STATUSES;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table ACCOUNT_TRANSACTION_STATUSES ( x int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table log;
drop table log
*
ERROR at line 1:
ORA-00942: table or view does not exist
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table log ( msg varchar2(20) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create trigger ddl_trigger before drop on schema
2 begin
3 insert into log values ( ora_dict_obj_name );
4 end;
5 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table ACCOUNT_TRANSACTION_STATUSES;
drop table ACCOUNT_TRANSACTION_STATUSES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01401: inserted value too large for column
ORA-06512: at line 2
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop trigger ddl_trigger;
Trigger dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table ACCOUNT_TRANSACTION_STATUSES;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>