Validate Oracle Column Names

Question

In one scenario we are dynamically creating sql to create temp tables on-fly. There is no issue with table_name as it is decided by us however the column-names are provided by sources not in our control.

Usually we would check the column names using below query:

select  ..
where NOT REGEXP_LIKE (Column_Name_String,'^([a-zA-Z])[a-zA-Z0-9_]*$') 
OR Column_Name_String is NULL
OR Length(Column_Name_String) > 30

However is there any build in function which can do a more extensive check. Also any input on the above query is welcome as well.

Thanks in advance.

---

Final query based on below answers:

select  ..
where NOT REGEXP_LIKE (Column_Name_String,'^([a-zA-Z])[a-zA-Z0-9_]{0,29}$') 
OR Column_Name_String is NULL
OR Upper(Column_Name_String) in (select Upper(RESERVED_WORDS.Keyword) from V$RESERVED_WORDS RESERVED_WORDS)

Particularly not happy with character's like $ in column name either hence won't be using..

dbms_assert.simple_sql_name('VALID_NAME')

Instead with regexp I can decide my own set of character's to allow.

Answer 1

This answer does not necessarily offer either a performance or logical improvement, but you can actually validate the column names using a single regex:

SELECT ...
WHERE NOT
    REGEXP_LIKE (COALESCE(Column_Name_String, ''), '^([a-zA-Z])[a-zA-Z0-9_]{0,29}$')

This works because:

• It uses the same pattern to match columns, i.e. starting with a letter and afterwards using only alphanumeric characters and underscore
• NULL column names are mapped to empty string, which fails the regex
• We use a length quantifier {0,29} to check the column length directly in the regex