Re: URGENT: Temporary Tables

From: Erik <ecotsonas_at_saraswati.com>
Date: Fri, 25 Jun 1999 18:05:30 GMT
Message-ID: <7l0gd0$b42$1@nnrp1.deja.com>

In article <7kvjh9$m2h$1_at_news3.saix.net>,   "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:
> yitbsal_at_statcan.ca wrote in message <7ku6r1$hd4$1_at_nnrp1.deja.com>...
> >
> >I want to create a temporary table -- visible only within a session
> >-- in a stored procedure. Is this possible?
>
> No.
>
> Now that was too easy to answer. That won't do in this newsgroup. So
let me
> try again... :-)
>
> temp tables? - ala SQL-Server style? No, not in Oracle.
>
> tables visible only within a session? - No (Oracle security works at
> user/schema level and not physical session/process level - you can
not grant
> rights or privs to a session)
>
> create a table in a stored proc? - Yes (using the dynamic SQL stored
proc
> package).
>
> Maybe if you tells us what the requirement is behind all this, we can
try
> and come up with alternative solutions?
>
> regards,
> Billy
>
>

Well, my interpretation of the question is a bit different than Billy's so here is my take on it. Yes, you can create a persistent pl/sql table (otherwise known as indexed-by table) for a session. Session being defined as a user's persistent connection (with out disconnecting, web transactions are not persistent). How do you do this?

1. Create a package with the necessary data types being global
2. Create the procedures/functions that manipulate the pl/sql table Note: As soon as the user disconnects the pl/sql table is gone, but as long as the user is connected the pl/sql table is visible to that session.

Here is an example:

[Snip]

---

CREATE OR REPLACE PACKAGE PERSISTENCE IS    PROCEDURE LOAD_ARRAY;
   PROCEDURE DISPLAY_ARRAY;

• GLOBAL VARIABLES type customer_rectype is record (cust_id number(5), cust_name varchar2(50), cust_birth_dt date );

  type customer_recs_tabtype is table of customer_rectype index by binary_integer;
  customer_recs customer_recs_tabtype;

END PERSISTENCE; /

CREATE OR REPLACE PACKAGE BODY PERSISTENCE AS PROCEDURE LOAD_ARRAY IS BEGIN

• Record 1 customer_recs(1).cust_id := 1000; customer_recs(1).cust_name := 'Erik'; customer_recs(1).cust_birth_dt := sysdate;
• Record 2 customer_recs(2).cust_id := 2000; customer_recs(2).cust_name := 'Erik'; customer_recs(2).cust_birth_dt := sysdate;

END LOAD_ARRAY;

---

---

PROCEDURE DISPLAY_ARRAY IS
BEGIN
   for i in 1..customer_recs.count loop

     dbms_output.put_line('*** Record ' || i || ' ***');
     dbms_output.put_line('CustID:   ' || customer_recs(i).cust_id);
     dbms_output.put_line('CustName: ' || customer_recs(i).cust_name);
     dbms_output.put_line('CustBirth: ' || customer_recs
(i).cust_birth_dt);
	 dbms_output.put_line(chr(10));

   end loop;
END DISPLAY_ARRAY; END PERSISTENCE; [Snip]

---

Here is the execution:

SQL> set serveroutput on;
SQL> exec persistence.load_array;

PL/SQL procedure successfully completed.

SQL> exec persistence.display_array;
*** Record 1 ***
CustID: 1000
CustName: Erik
CustBirth: 25-JUN-99

• Record 2 *** CustID: 2000 CustName: Erik CustBirth: 25-JUN-99

PL/SQL procedure successfully completed.

SQL> exec persistence.display_array;
*** Record 1 ***
CustID: 1000
CustName: Erik
CustBirth: 25-JUN-99

• Record 2 *** CustID: 2000 CustName: Erik CustBirth: 25-JUN-99

PL/SQL procedure successfully completed.

SQL> --
Erik
Consultant
Saraswati Systems Corporation - (SSC)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 25 1999 - 13:05:30 CDT