Monday, 8 September 2014

Convert LONG to VARCHAR2 in an SQL Statement

"Why are you still using Long column types, they've been extinct for ages?"
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 

No comments: