trigger on create table and modify column name 1

[huchen]

Hello,

Oracle allow table name length to 30 and column name length to 30.

I need to limit the table name and column name size to 27 for any new tables and new columns in my database.

Can I write a trigger on create table and new column to check the size? How can I do it?

Thank you in advance.

 

[LKBrwnDBA]

You could create a DDL Event trigger.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[huchen]

Thank you LKBrwnDBA. It works for me. Here is the trigger I wrote:

CREATE OR REPLACE TRIGGER
trg_ddl_check_name_length
AFTER CREATE OR ALTER
ON SCHEMA
DECLARE
oper VARCHAR2(30);
sql_text ora_name_list_t;
i PLS_INTEGER;
obj_name VARCHAR2(100);
v_owner VARCHAR2(100);
BEGIN
/* if table/column name is more than 28 byte, send email to DBAs to
reduce the size

This trigger is only for DEV
*/
SELECT ora_sysevent, ora_dict_obj_name,ora_dict_obj_owner
INTO oper ,obj_name ,v_owner
FROM dual;
i := sql_txt(sql_text);
IF oper IN ('CREATE', 'ALTER') THEN
IF length(obj_name)>28 THEN
sp_send_mail_generic
('DEV',
'LKBrwnDBA@tek-tips.com',
'Alert --from trg_ddl_check_name_length',
'reduce the name size to 28 byte: '||sql_text(1));
END IF;
FOR i IN (SELECT column_name from dba_tab_columns
where owner =v_owner and table_name =obj_name ) LOOP
IF length(i.column_name)>28 THEN
sp_send_mail_generic
('DEV',
'LKBrwnDBA@tek-tips.com',
'Alert --from trg_ddl_check_name_length',
'reduce column size to 28 byte: '||v_owner||'.'||obj_name||'.' ||i.column_name);
END IF;
END LOOP;
END IF;
END trg_ddl_check_name_length;