Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work

From: Larry Jones <lljo_at_chevron.com>
Date: 1997/07/30
Message-ID: <33DF2931.9B7_at_chevron.com>#1/1


I am dropping the table, b/c it is created under the user's id for temporary use, so a report can be created. I cannot go to each user's id and create the table permanently. Nor could I create the table under the schema id permanently b/c the number of users using the system at any one time and trying to store data in this "temp" table would make it almost impossible to size it correctly.

John Caputo wrote:
>
> Why do you drop the table? What I would try here is to create the table
> as a permanent one, since you appear to want to run this many times by
> passing in a parameter. Don't drop the table in your procedure. Truncate
> it instead. This way the definition of the table is always around but
> you can selectively empty the data from it either in your procedure or
> other method (SQL*Plus, etc.).
>
> Since the table is created outside the the procedure, you shouldn't have
> any trouble with ORA-00942. If you like this idea, let me know if it
> worked ok.
>
> ---------------------------------------------------------------
> John Caputo Lawrence Livermore
> Email: caputo1_at_llnl.gov National Laboratory
> Phone: (510) 422-5262 P.O.Box 808, L-650
> Mon-Thu 7:30 AM-5:30 PM - Off Fri Livermore, CA 94551
 

-- 
**********************
Larry Jones
lljo_at_chevron.com
Received on Wed Jul 30 1997 - 00:00:00 CEST

Original text of this message