| 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) |
