Oracle | Converting The LONG RAW Data Type To CLOB

Oracle Data Type LONG RAW To CLOB Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Oracle has advised, since 8i, that the LONG RAW datatype no longer be used. This demo is included for those still working with legacy system that contain the LONG RAW data type.

Conversion Demo

Demo Table with the LONG RAW Data Type conn uwclass/uwclass@pdbdev CREATE TABLE lr_demo(testcol LONG RAW);

Load Demo Table with the LONG RAW Column INSERT INTO lr_demo (testcol) VALUES (utl_raw.cast_to_raw('Demo text for testing')); COMMIT; SELECT * FROM lr_demo;

Demo Global Temporary Table with a BLOB Column CREATE GLOBAL TEMPORARY TABLE temp_data(lob BLOB) ON COMMIT DELETE ROWS;

Parent Stored Procedure CREATE OR REPLACE PROCEDURE lr2text AUTHID CURRENT_USER IS destLOB CLOB; srcBLOB BLOB; srcSize INTEGER := dbms_lob.lobMaxSize; srcOset INTEGER := 1; dstOset INTEGER := 1; blobCSID NUMBER := dbms_lob.default_csid; langCtx INTEGER := dbms_lob.default_lang_ctx; warning INTEGER; BEGIN INSERT INTO temp_data SELECT TO_LOB(testcol) FROM lr_demo; SELECT lob INTO srcBLOB FROM temp_data; dbms_lob.createTemporary(destLOB, TRUE); dbms_lob.converttoclob(destLOB, srcBLOB, srcSize, srcOset, dstOset, blobCSID, langCtx, warning); dbms_output.put_line(destLOB); END lr2text; /

Run The Demo set serveroutput on exec lr2text; SELECT COUNT(*) FROM temp_data; COMMIT; SELECT COUNT(*) FROM temp_data;