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