Re: DDL statement in Stored Procedure.

The user owning your procedure needs CREATE TABLE privilege granted directly to be able to create tables from stored procedures. By the way, if these tables are intended to be used for intersession communications, don't use this approach. There are many other (more effective) ways to implement this. One simple way is to create one table like this:

create table t_dump (l_tind number, rqindx number)

and insert data into it instead of separate table for each l_tind. Or use Advanced Queueing.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Manish" <manish1000_at_hotmail.com> wrote in message
news:9d90ea9a.0204090652.7d8af5c9_at_posting.google.com...


> Daniel Morgan <damorgan_at_exesolutions.com> wrote in message

news:<3CB208B9.B0B2A4D4_at_exesolutions.com>...


> > The problem is not just one of privileges.

> >

> > Equally important is that you undoubtedly come from a background of SQL

> > Server or Sybase and have not learned Oracle's architecture. What you are

> > trying to do is not done in Oracle ... ever. It is not that you can't ...

> > which gets into the issue of privileges ... but rather that is is a great

> > way to create an unscalable application with lousy performance.

> >

> > You can look up global temporary tables if you wish ... but I think it

> > far better investment of your time would be in just forgetting the entire

> > idea and doing whatever it is you want to do in the Oracle way.

> >

> > Daniel Morgan

> >

> >

> >

> > Manish wrote:

>

>  Thanks for ur comments but it did not solve the problem. The Global

> Temporary tables can be created at session or transaction level and

> also its data can not be seen by other transactions. Hence I want to

> create a permanent table thru stored procedure with variable name. If

> anybody tell me the method to create the table in Stored Procedure

> either by using "Execute Immediate" or by "DBMS_SQL" package, it will

> be great help. I am able to create the same by PL/SQL but it is giving

> "Insufficient Previlidges" message when I try to use Stored Procedure.

>

> Thanks.

>

> Manish