Trigger to prevent edit:keywords being used as Column Names

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/[email protected]_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/[email protected]_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.