Re: Howto Create global temporary table in Oracle 8.0.5

From: Jimbo1 <nightfanguk_at_yahoo.co.uk>
Date: 15 Dec 2005 10:41:47 -0800
Message-ID: <1134672106.786799.256050@g49g2000cwa.googlegroups.com>

Hi Claude,

It worked. The PL/SQL table will persist in a package across the same session. I thought it might, but wasn't 100% certain.

The script I used to test this is below, along with the output.

Cheers.

James

TEST SCRIPT

---

CREATE TABLE alcoholic_drinkypoohs (

   key NUMBER PRIMARY KEY,
   name VARCHAR2(50)
);

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 1, 'Schnapse' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 2, 'John Smiths' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 3, 'Jack Daniels' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 4, 'Boddingtons' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 5, 'Kronenberg 1664' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 6, 'Becks' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 7, 'Smirnoff Ice' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 8, 'Aftershock - Never again!' );

INSERT INTO alcoholic_drinkypoohs

     VALUES ( 9, 'WKD' ); INSERT INTO alcoholic_drinkypoohs

     VALUES ( 10, 'Guiness' );

COMMIT; CREATE TABLE deleted_drinks (

   key NUMBER PRIMARY KEY,
   name VARCHAR2(50)
);

CREATE OR REPLACE
PACKAGE pkg_collection_test
IS

   PROCEDURE before_trigger( p_old_record_in IN alcoholic_drinkypoohs%ROWTYPE );

   PROCEDURE after_trigger;

END pkg_collection_test;
/

CREATE OR REPLACE
PACKAGE BODY pkg_collection_test
IS

   TYPE tt_record_tabtype IS TABLE OF alcoholic_drinkypoohs%ROWTYPE

      INDEX BY BINARY_INTEGER;    tb_deleted_records tt_record_tabtype;

   ln_index BINARY_INTEGER := 0;

   PROCEDURE before_trigger( p_old_record_in IN alcoholic_drinkypoohs%ROWTYPE )

   IS
   BEGIN

      IF tb_deleted_records.EXISTS(0)
      THEN
         ln_index := tb_deleted_records.LAST + 1;
         tb_deleted_records(ln_index) := p_old_record_in;
      ELSE
         tb_deleted_records(0) := p_old_record_in;
      END IF;

   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE_APPLICATION_ERROR( -20001, 'Oracle Exception occurred in

PKG_COLLECTION_TEST.BEFORE_TRIGGER:'||CHR(10)||SQLERRM );    END before_trigger;

   PROCEDURE after_trigger
   IS
   BEGIN

      FOR i IN tb_deleted_records.FIRST..tb_deleted_records.LAST
      LOOP
         INSERT INTO deleted_drinks
              VALUES ( tb_deleted_records(i).key,
tb_deleted_records(i).name );
      END LOOP;

   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE_APPLICATION_ERROR( -20002, 'Oracle Exception occurred in

PKG_COLLECTION_TEST.AFTER_TRIGGER:'||CHR(10)||SQLERRM );    END after_trigger;

END pkg_collection_test;
/

CREATE OR REPLACE
TRIGGER trg_alc_before_delete
BEFORE DELETE ON alcoholic_drinkypoohs
REFERENCING OLD AS old
FOR EACH ROW
DECLARE
   rc_old_record alcoholic_drinkypoohs%ROWTYPE; BEGIN
   rc_old_record.key := :old.key;
   rc_old_record.name := :old.name;

   pkg_collection_test.before_trigger( rc_old_record );

END trg_alc_before_delete;
/

CREATE OR REPLACE
TRIGGER trg_alc_after_delete
AFTER DELETE ON alcoholic_drinkypoohs
BEGIN
   pkg_collection_test.after_trigger;
END trg_alc_after_delete;
/

TEST OUTPUT

---

SQL> SELECT * FROM deleted_drinks;

no rows selected

SQL> SELECT * FROM ALCOHOLIC_DRINKYPOOHS;        KEY NAME

---------- --------------------------------------------------
         1 Schnapse
         2 John Smiths
         3 Jack Daniels
         4 Boddingtons
         5 Kronenberg 1664
         6 Becks
         7 Smirnoff Ice
         8 Aftershock - Never again!
         9 WKD
        10 Guiness

10 rows selected.

SQL> DELETE FROM ALCOHOLIC_DRINKYPOOHS; 10 rows deleted.

SQL> COMMIT; Commit complete.

SQL> SELECT * FROM ALCOHOLIC_DRINKYPOOHS; no rows selected

SQL> SELECT * FROM deleted_drinks;

       KEY NAME

---------- --------------------------------------------------
         1 Schnapse
         2 John Smiths
         3 Jack Daniels
         4 Boddingtons
         5 Kronenberg 1664
         6 Becks
         7 Smirnoff Ice
         8 Aftershock - Never again!
         9 WKD
        10 Guiness

10 rows selected. Received on Thu Dec 15 2005 - 12:41:47 CST