Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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?

Comments

Post Details

Added on Mar 26 2020
14 comments
611 views