Convert LONG datatypes to LOB's or Query on the Long Data type Columns

CREATE TABLE XX_LOG_STG(C1 NUMBER, C2 LONG)
/
INSERT INTO  XX_LOG_STG VALUES (1, 'LONG data to convert to CLOB')
/
-- Insert LONG into LOB column
CREATE TABLE XX_LOB_STG(C1 NUMBER, C2 CLOB);

-- Use TO_LOB function to convert LONG to LOB...
INSERT INTO  XX_LOB_STG
       SELECT C1, TO_LOB(C2) FROM XX_LOG_STG;

CREATE TABLE XX_DBA_TRIGGERS AS SELECT TRIGGER_NAME, TO_LOB(TRIGGER_BODY) AS TRIGGER_BODY

FROM DBA_TRIGGERS ;

-- Query on the Long Data type Column i.e. Trigger Body as below




SELECT * FROM XX_DBA_TRIGGERS WHERE UPPER(TRIGGER_BODY) LIKE '%FND_REQUEST%' ;