Re: DDL statement in Stored Procedure.

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:

> Hi
>
> I was trying to create a table (Dynamic DDL) thru a stored procedure
> as the name of the table will be known at run-time as the following :
>
> CREATE OR REPLACE PROCEDURE TESTPROC
> ( L_TIND INTEGER ) AS
> L_SQL VARCHAR2 (2000); L_TABLENAME VARCHAR2 (25);
> BEGIN
> L_TABLENAME := 'TBL_' || TO_CHAR( L_TIND );
> L_SQL := 'CREATE TABLE ' || L_TABLENAME || ' ( RQINDX NUMBER )';
> EXECUTE IMMEDIATE L_SQL;
> END;
> /
>
> It is giving the error message as "ORA-01031: Insufficient
> Previlidges". When I comment the line containing "EXECUTE IMMEDIATE
> L_SQL;" then it works. Can any one tell me what type of previlidges
> are required here?
>
> Any help will be appreciated. Thanks.
>
> Manish Gupta
Received on Mon Apr 08 2002 - 16:16:43 CDT