陈君的證婚詞

陈君的證婚詞

雁山高聳 甌水長東
陳君超量 葛女邊紅
俊士鶴立 仙子臨風
兩情想悅 靈犀相通
攜手三生 比翼長空
良緣天作 其樂融融
見證今宵 盛典躬逢
金玉滿堂 春色蔥朧

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>