| 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@pdbdev
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@pdbdev
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;
/ |
