Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: privileges and EXECUTE IMMEDIATE 'CREATE...'
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