CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle LONG To CLOB
Version 11.1
 
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 Table With LONG CREATE TABLE t1 (x INT, y LONG);
Load Demo Table With LONG 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 CLOB CREATE GLOBAL TEMPORARY TABLE t2
(x INT, y CLOB)
ON COMMIT DELETE ROWS;
Create REF CURSOR Type

Use the REF CURSOR data type in 9i rather than this construct.
CREATE OR REPLACE PACKAGE uw_type 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)
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) 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)
 
Related Topics
DBMS_LOB
DBMS_METADATA_UTIL
UTL_XML
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [77 users online]    © 2010 psoug.org