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

Larry Jones wrote:
>
> HELP!!
>
> Several of you tried to help me with an earlier problem, where I had a
> huge SQL statement in
> which I was trying to return rows, as columns.
>
> After beating my head against that wall for a while, I took the
> suggestions of many of you,
> and started using a temporary table. I wrote the SQL, and it worked
> great. But then when I
> put this into a stored procedure, it bombs!!!! The exact same code!
>
> Can anyone look at this, and tell me what in the world is going on?
>

[Quoted] 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