Forum Stats

  • 3,825,167 Users
  • 2,260,475 Discussions
  • 7,896,430 Comments

Discussions

Trigger to prevent edit:keywords being used as Column Names

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Mar 30, 2020 7:32AM

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

    SY.

  • 4218587
    4218587 Member Posts: 6
    edited Apr 2, 2020 6:19AM

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

  • Paulzip
    Paulzip Member Posts: 8,690 Blue Diamond
    edited Apr 2, 2020 7:59AM

    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Apr 2, 2020 9:29AM

    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.