Grab Tablename From Stored SELECT Statement Using Oracle’s REGEXP Functions

I have an sqltext column in my database that contains select statements like

• select firstname, lastname from employees

• select firstname, lastname from managers
  where empid = 21

I need to extract the tablenames from these statements. Here is my cheeseball solution:

select
trim(REGEXP_REPLACE(replace(sqltext,chr(10),' '),
'(.*)( from )(.*)( where .*)' ||case when instr(replace(sqltext,chr(10), ' '), ' where ') = 0 then '?' end,
'-\3-',1,0,'i'))
from someTableThatContainsSqlText


Advertisement