privileges and EXECUTE IMMEDIATE 'CREATE...'

From: <veraon_at_hotmail.com>
Date: Fri, 20 Aug 1999 01:58:22 GMT
Message-ID: <7picnq$rne$1@nnrp1.deja.com>

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 Thu Aug 19 1999 - 20:58:22 CDT