Re: DDL statement in Stored Procedure.

On 8 Apr 2002 14:02:56 -0700, manish1000_at_hotmail.com (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

This question has been answered over and over and over and over again. You need *direct* privilege, not by means of a role, roles are disabled during compilation of a stored procedure.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Apr 08 2002 - 16:14:53 CDT