Oracle Data Type LONG To CLOB
Version 11.2.0.3

General Information
Oracle has advised, since 8i, that the LONG datatype no longer be used. This demo is included for those still working with legacy system that contain the LONG data type.
 
Demo 1
Demo Table with the LONG Data Type conn uwclass/uwclass

CREATE TABLE t1 (x INT, y LONG);
Load Demo Table with the LONG Column INSERT INTO t1 VALUES (1, RPAD('*',100,'*'));
INSERT INTO t1 VALUES (2, RPAD('*',100,'$'));
INSERT INTO t1 VALUES (3, RPAD('*',100,'#'));
COMMIT;
Demo Table with a CLOB Column CREATE GLOBAL TEMPORARY TABLE t2
(x INT, y CLOB)
ON COMMIT DELETE ROWS;
Create REF CURSOR Type CREATE OR REPLACE PACKAGE uw_type AUTHID CURRENT_USER IS
 TYPE t_ref_cursor IS REF CURSOR;
END uw_type;
/
Child Stored Procedure CREATE OR REPLACE PROCEDURE child(p_NumRecs IN PLS_INTEGER,
p_return_cur OUT uw_type.t_ref_cursor) AUTHID CURRENT_USER
IS
BEGIN
  INSERT INTO t2
  SELECT x, TO_LOB(y)
  FROM t1
  WHERE x = p_NumRecs;

  OPEN p_return_cur FOR 'SELECT * FROM t2';
END child;
/
Parent Stored Procedure CREATE OR REPLACE PROCEDURE parent(pNumRecs PLS_INTEGER) AUTHID CURRENT_USER IS
 p_retcur uw_type.t_ref_cursor;
 at_rec   t2%ROWTYPE;
 NumRows  PLS_INTEGER;
BEGIN
  -- empty the global temporary table
  COMMIT;

  child(pNumRecs, p_retcur);

  SELECT COUNT(*)
  INTO NumRows
  FROM t2;

  FOR i IN 1 .. NumRows LOOP
    FETCH p_retcur INTO at_rec;
    dbms_output.put_line(at_rec.x || ' - ' || at_rec.y);
  END LOOP;
END parent;
/
Run The Demo set serveroutput on

exec parent(2)
exec parent(3)
exec parent(1)
exec parent(2)
 
Demo 2
Run The Demo conn uwclass/uwclass

CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id       NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date     DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
PARTITION yr7 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION yr8 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION yr9 VALUES LESS THAN (MAXVALUE));

desc user_tab_partitions

set serveroutput on

DECLARE
 c CLOB;
BEGIN
  FOR prec IN (SELECT high_value FROM user_tab_partitions) LOOP
    c := prec.high_value;
    dbms_output.put_line(c);
  END LOOP;
END;
/

Related Topics
DBMS_LOB
DBMS_METADATA_UTIL
LONG RAW to CLOB
UTL_XML.LONG2CLOB

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved