Trigger to prevent edit:keywords being used as Column Names

4218587 · Mar 26, 2020 6:34AM

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 · Mar 26, 2020 7:48AM

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

SY.

4218587 · Mar 26, 2020 7:51AM

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 · Mar 26, 2020 7:52AM

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 · Mar 26, 2020 7:58AM

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 · Mar 26, 2020 8:02AM

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

Solomon Yakobson · Mar 26, 2020 8:10AM

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 · Mar 26, 2020 8:20AM

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 · Mar 26, 2020 9:13AM

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

is

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 · Mar 26, 2020 11:36AM

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 · Mar 30, 2020 5:50AM

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

Forum Stats

Quick Links

Categories

Trigger to prevent edit:keywords being used as Column Names

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Trigger to prevent edit:keywords being used as Column Names

Answers