SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger to prevent edit:keywords being used as Column Names

4218587Mar 26 2020 — edited Apr 2 2020

I am trying to create a trigger with Regex to prevent tables being created with a column name (quoted or unquoted) that is a reserved word (e.g. ACCOUNT).

I have the trigger part worked out but I am struggling with the regex part. I have the following so far SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(PARTIAL|LIST|OF|RESERVED|WORDS)\s*"', 1, 1, 0, 'i') into l_ret2 from dual; Which works fine for quoted words.

If I try with unquoted words, for some reason it blocks when I try to add a primary key SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)(PRIMARY)\s', 1, 1, 0, 'i') into l_ret2 from dual; I added a negative lookahead but all that does is allow the name PRIMARY as a column name SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)\s*(PRIMARY(?!\s+KEY))', 1, 1, 0, 'i') into l_ret2 from dual;

So my question is what is wrong with my regex, or even better has someone already made a trigger to do what I want?

Solomon Yakobson

You can't. Syntax is checked before trigger fires.

SY.

4218587

Are you sure? I've gotten this to work in almost all cases, it's just struggling with PRIMARY KEY and CONSTRANT <name> CHECK.

Solomon Yakobson

Actually your post is confusing. You say "Trigger to prevent reserved words being used as Column Names" but then using column name account which is keyword but is not reserved.

SY.

Solomon Yakobson

As I said, you are confusing keyword with reserved word. For example:

SQL> create or replace

2 trigger before_create

3 before create

4 on database

5 begin

6 dbms_output.put_line('Trigger fired');

7 end;

8 /

Trigger created.

SQL> set serveroutput on

SQL> create table tbl(column number)

2 /

create table tbl(column number)

ERROR at line 1:

ORA-00904: : invalid identifier

SQL> create table tbl(account number)

2 /

Trigger fired

Table created.

SQL>

As you can see error is raised before trigger can fire if column name is reserved word and trigger doesn't fire. It will fire if column name is a keyword but isn't reserved. So please clarify what are you trying to achieve.

SY.

4218587

Sorry, a mistake on my part. I do indeed want to prevent Keywords being used as column names, not reserved words.

Solomon Yakobson

af497e6c-eb39-4a40-8947-65fc200a1bfc wrote:

Sorry, a mistake on my part. I do indeed want to prevent Keywords being used as column names, not reserved words.

Post trigger code.

SY.

4218587

create or replace trigger no_key_in_name

before create on database

DECLARE

sql_text ora_name_list_t;

n PLS_INTEGER;

v_stmt CLOB;

v_stmt2 CLOB;

l_ret2 PLS_INTEGER;

BEGIN

n := ora_sql_txt(sql_text);

FOR i IN 1..n LOOP

v_stmt := v_stmt || sql_text(i);

END LOOP;

v_stmt2 := replace(v_stmt,chr(13),' ');

v_stmt2 := replace(v_stmt2,chr(10),' ');

l_ret2 := 0;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 < 1 THEN

END IF;

IF l_ret2 > 0 THEN

RAISE_APPLICATION_ERROR(-20999, 'Stall the Ball. You are trying to create a new table '||ora_dict_obj_owner||'.'||ora_dict_obj_name

||chr(10)||'

with a forbidden column name. '

||chr(10)||' Contact your DBA please if you need help!');

END IF;

END;

Solomon Yakobson

It is virtually impossible to parse CREATE TABLE statement for keywords in general. What if someone issues CTAS

CREATE TABLE TBL AS SELECT expression list ...

Now we have to parse expression list looking for column aliases (explicit or implicit). And even if it is plain

CREATE TABLE(....)

it can be something like:

create table tbl(col1 number generated always as (account()),col2 date);

where account is function name. You should use AFTER CREATE:

create or replace

trigger no_key_in_name

after create

on database

declare

cursor v_cur

select column_name

from dba_tab_cols c,

v$reserved_words w

where c.owner = ora_dict_obj_owner

and c.table_name = ora_dict_obj_name

and upper(c.column_name) = w.keyword

and w.reserved = 'N';

begin

-- We are interested in CREATE TABLE only

if ora_dict_obj_type = 'TABLE'

then

for v_rec in v_cur loop

dbms_output.put_line('Column name ' || v_rec.column_name || ' is a keyword.');

end loop;

end if;

end;

Now:

set serveroutput on

create table tbl(

active varchar2(1),

account_id number generated always as (account()),

dt date

)

Column name ACTIVE is a keyword.

Table created.

SQL>

SY.

EdStevens

Seems to me that it might be easier to create a daily job that simply scans DBA_TAB_COLS for any column name that is a key word. If any hits are found, email the DBA team, and they can take corrective action -- like introducing the devs to Billy's infamous lead pipe.

4218587

I am getting the following error. https://imgur.com/a/78JmksM

Solomon Yakobson

Trigger owner must be granted (directly, not via role) select on DBA_TAB_COLS and V$RESERVED_WORDS.

SY.

4218587

I am getting the same error even after granting the permissions directly

Paulzip

SQL>

SQL> conn sys/sys_password@my_schema as sysdba;

Connected.

SQL>

SQL> grant select on dba_tab_cols to PAULZIP

2 /

Grant succeeded.

SQL>

SQL> grant select on v_$reserved_words to PAULZIP -- Because V$RESERVED_WORDS is a synonym

2 /

Grant succeeded.

SQL>

SQL> conn paulzip/my_password@my_schema

Connected.

SQL>

SQL> create or replace trigger trgacd_reserved_words_check after create on database

2 declare

3 vColList varchar2(4000 byte);

4 begin

5 -- We are interested in CREATE TABLE only

6 if ora_dict_obj_type = 'TABLE' then

7 select listagg(c.column_name, ',') within group (order by column_id) -- Would allow around 30 columns, should be more than enough

8 into vColList

9 from dba_tab_cols c,

10 v$reserved_words w

11 where c.owner = ora_dict_obj_owner

12 and c.table_name = ora_dict_obj_name

13 and upper(c.column_name) = w.keyword

14 and w.reserved = 'Y';

15 if vColList is not null then

16 raise_application_error(-20100, 'Table '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' contains columns which are reserved words : '||chr(13)||chr(10)||vColList);

17 end if;

18 end if;

19 end;

20 /

Trigger created.

SQL>

SQL> create table t_test_reserved (

2 ID integer not null, -- OK

3 "PRIOR" integer, -- Reserved

4 DESCRIPTION varchar2(50), -- OK

5 "MODE" integer, -- Reserved

6 "UNIQUE" integer -- reserved

7 )

8 /

create table t_test_reserved (

ERROR at line 1:

ORA-04088: error during execution of trigger 'PAULZIP.TRGACD_RESERVED_WORDS_CHECK'

ORA-00604: error occurred at recursive SQL level 1

ORA-20100: Table PAULZIP.T_TEST_RESERVED contains columns which are reserved words :

PRIOR,MODE,UNIQUE

ORA-06512: at line 15

SQL>

SQL> create table t_test_reserved_2 as

2 select 1 as "CLUSTER", 2 as "SET"

3 from dual

4 connect by level < 2

5 /

from dual

ERROR at line 3:

ORA-04088: error during execution of trigger 'PAULZIP.TRGACD_RESERVED_WORDS_CHECK'

ORA-00604: error occurred at recursive SQL level 1

ORA-20100: Table PAULZIP.T_TEST_RESERVED_2 contains columns which are reserved words :

CLUSTER,SET

ORA-06512: at line 15

SQL>

SQL> create table t_test_reserved_3 (

2 ID integer not null, -- OK

3 DESCRIPTION varchar2(50) -- OK

4 )

5 /

Table created.

Solomon Yakobson

Show us a snippet where you directly grant select on DBA_TAB_COLS and V$RESERVED_WORDS to trigger owner and then create trigger along with error messages.

SY.

1 - 14

Added on Mar 26 2020

#plsql, #trigger

14 comments

637 views

SQL & PL/SQL

Trigger to prevent edit:keywords being used as Column Names

Comments

Post Details