I'm not but the data dictionary is...
Anyway, I struggled for ages to find a solution to quickly convert Long column types to Varchar2 on the fly in an SQL statement without creating some kind of PLSQL function to do the job, which I didn't want to do. So I came up with converting it to XML then querying the XML. Still not ideal but got the job done without me having to create PLSQL.
WITH
xml AS
( SELECT DBMS_XMLGEN.GETXMLTYPE(
'SELECT * FROM dba_triggers where trigger_name like ''%MODTRG''' ) AS xml
FROM DUAL
),
dbaTriggersData AS
( SELECT xs.trigger_name,
xs.trigger_body
FROM xml x,
XMLTABLE('/ROWSET/ROW'
PASSING x.xml
COLUMNS trigger_name VARCHAR2(30) PATH 'TRIGGER_NAME',
trigger_body VARCHAR2(4000) PATH 'TRIGGER_BODY') xs
)
SELECT *
FROM dbaTriggersData
WHERE trigger_body LIKE '%USER%'
AND trigger_body NOT LIKE '%APP_USER%'
;
This particular query was to check my triggers were ok for Apex. Referencing APP_USER if they referenced USER
Posts
