Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DDL statement in Stored Procedure.

Re: DDL statement in Stored Procedure.

From: Kenny Yu <kyu_at_biodiscovery.com>
Date: Thu, 2 May 2002 15:24:12 -0700
Message-ID: <ud3f1mofdgiufa@corp.supernews.com>


We have to recognize the need for executing dynamic sql, ddl in particular. TRUNCATE TABLE and DROP/CREATE INDEX are interesting candidates for the operations.

What you need is
GRANT CREATE TABLE TO "USER_NAME"
Remember, the privileges that come from ROLEs are lost when you run a PL/SQL package. You need specific system or object privs in PL/SQL.

Kenny

"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:

> 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 Thu May 02 2002 - 17:24:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US