Re: Howto Create global temporary table in Oracle 8.0.5

Hi Claude,

How are you actually deleting from this table, i.e. are you deleting from within PL/SQL, or could this table be deleted from outside of PL/SQL? For example, if somebody was to delete a row from this table in SQLplus, would you want to see the behaviour you describe taking place? Is anybody likely to delete from this table on an ad-hoc basis in the live app?

The reason I ask is that there is an SQL clause available to PL/SQL called "RETURNING <column_list> INTO <variable_list>;" that you might be able to use. The reason I say 'might' is that I can't remember whether this was a new feature in Oracle 8.0 or 8i? If you are only carrying out deletions from PL/SQL, you will be able to use this clause, and it would be much more elegent than creating two triggers.

Here's a quick test for you to try to see if this is an option. If you get an exception at Point 10 in the script below, then it's probably a non-starter and I'll try to think of something else.

Cheers.

James

• Create Simple Test Table. CREATE TABLE alcohol ( key NUMBER PRIMARY KEY, name VARCHAR2(50) );
• Populate Test Table with two rows. INSERT INTO alcohol VALUES ( 1, 'John Smiths');

INSERT INTO alcohol

     VALUES ( 2, 'Jack Daniels' );

COMMIT;

• Enable ability to see DBMS_OUTPUT, errm, output. SET SERVEROUTPUT ON
• Create Test PL/SQL block; DECLARE rc_test_record alcohol%ROWTYPE; ln_point INTEGER; BEGIN ln_point := 10;

   DELETE FROM alcohol

         WHERE key = 1
     RETURNING key, name INTO rc_test_record.key, rc_test_record.name;

   COMMIT;    ln_point := 20;

   DBMS_OUTPUT.PUT_LINE( 'rc_test_record.key =
'||TO_CHAR(rc_test_record.key) );
   DBMS_OUTPUT.PUT_LINE( 'rc_test_record.name = '||rc_test_record.name

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE('Oracle exception occured at Point '||TO_CHAR(ln_point)|| ' in test script:'||CHR(10)||SQLERRM ); END;
/ Received on Thu Dec 15 2005 - 08:09:02 CST