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: privileges and EXECUTE IMMEDIATE 'CREATE...'

Re: privileges and EXECUTE IMMEDIATE 'CREATE...'

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Fri, 20 Aug 1999 13:25:33 GMT
Message-ID: <37BD574D.E1711D07@edcmail.cr.usgs.gov>


The create command is a DDL command. You can not execute DDL commands from within a procedure/function/package. To do that, you need to use the DBMS_SQL package which will parse and execute your DDL commands.

HTH,
Brian

veraon_at_hotmail.com wrote:
>
> Hi, all!:
> I use SQL*Plus.
> In the same session I can create sequence if it is stand alone
> statement. But if I execute it dinamically in the procedure I get an
> answer that there is not enough privileges. You can see it from
> fragment of SQL*plus session below.
> Thank you for any idea.
> Vera
>
> SQL> create sequence seq minvalue 1 maxvalue
> 1000000000000000000000000000 increment by 1 start with 1;
>
> if i remove line #9 it works fine.
>
> SQL> edit
> Wrote file afiedt.buf
>
> 1 create or replace procedure seq_test
> 2 is
> 3 v_T integer;
> 4 begin
> 5 select count(1) into v_T from user_sequences where sequence_name
> = 'SEQ';
> 6 if v_T = 1 then
> 7 /* lock table THETABLE in exclusive mode;*/
> 8 EXECUTE IMMEDIATE 'drop sequence seq';
> 9 EXECUTE IMMEDIATE 'create sequence seq minvalue 1 maxvalue
> 1000000000000000000000000000 increment by 1 start with 10;
> 10 commit;
> 11 else
> 12 dbms_output.put_line ('There is no sequence');
> 13 end if;
> 14* end;
> SQL> /
>
> Procedure created.
>
> SQL> exec seq_test
> begin seq_test; end;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "RENTAGENT.SEQ_TEST", line 9
> ORA-06512: at line 1
>
> SQL> create sequence seq minvalue 1 maxvalue
> 1000000000000000000000000000 increment by 1 start with 8;
>
> Sequence created.
>
> SQL>
>
> I removed line #9:
>
> SQL> edit
> Wrote file afiedt.buf
>
> 1 create or replace procedure seq_test
> 2 is
> 3 v_T integer;
> 4 begin
> 5 select count(1) into v_T from user_sequences where sequence_name
> = 'SEQ';
> 6 if v_T = 1 then
> 7 /*lock table THETABLE in exclusive mode;*/
> 8 EXECUTE IMMEDIATE 'drop sequence seq';
> 9 commit;
> 10 else
> 11 dbms_output.put_line ('There is no sequence');
> 12 end if;
> 13* end;
> 14 /
>
> Procedure created.
>
> SQL> exec seq_test
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Aug 20 1999 - 08:25:33 CDT

Original text of this message

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