Using dynamic query to create sequence

tinku981

Hello,

I created the sequence dynamically in a Procedure, but when I executed, it gave me an Insufficient privileges error:


SQL> create table dummy (id number, module_id varchar2(20), p_order number, status varchar2(1));

SQL> insert into dummy values (10, 'test', 0, 'D');

SQL> CREATE OR REPLACE PROCEDURE PRO_SEQ_ARRNGE(P_ID NUMBER) AS
V_MOD DUMMY.MODULE_ID%TYPE;
v_query1 varchar2(200);
v_query2 varchar2(200);
V_COUNT NUMBER;
begin
v_query1 := 'drop sequence unqid';
v_query2 := 'create sequence unqid start with 1 increment by 1 minvalue 1';
SELECT COUNT(*)
INTO V_COUNT
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'UNQID';

IF V_COUNT = 0 THEN
execute immediate v_query2;
ELSE
execute immediate v_query1;
execute immediate v_query2;
END IF;

SELECT distinct MODULE_ID INTO V_MOD FROM DUMMY WHERE ID = P_ID;

update dummy
set P_order = 0, status = 'D'
WHERE ID = P_ID
and module_id = v_mod;
--COMMIT;

execute immediate 'UPDATE DUMMY SET P_ORDER = UNQID.NEXTVAL WHERE MODULE_ID = V_MOD AND STATUS = ''A''';
--COMMIT;

END PRO_SEQ_ARRNGE;


SQL> exec PRO_SEQ_ARRNGE(10);
BEGIN PRO_SEQ_ARRNGE(10); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.PRO_SEQ_ARRNGE", line 15
ORA-06512: at line 1

Can you please advise how to resolve it?


Thanks in advance,
Tinku

SomeoneElse

Can you please advise how to resolve it?

Yuck, you're making this unnecessarily difficult.

Create the sequence once outside of pl/sql and be done with it forever.

tinku981

I understand that, but in that case it is required for me to create a sequence inside the procedure. Can you please suggest, how I can solve my problem?

Thanks in advance,

sb92075

ORA-06512: at "SYSTEM.PRO_SEQ_ARRNGE", line 15

You should never use SYS or SYSTEM for application activities.

Unknown

Give the user the proper privileges.

tinku981

The required rights are there!

Did you tried it?

sb92075

tinku981 wrote:
The required rights are there!

Oracle disagrees

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.PRO_SEQ_ARRNGE", line 15

Either Oracle is correct & you are mistaken
or Oracle incorrectly reports error where none exists & you need to file Bug Report.

I bet that Oracle better reports reality than you.

JustinCave

When I try it, I get a different error

SQL> create table dummy (id number, module_id varchar2(20), p_order number, status varchar2(1));

Table created.

SQL>  insert into dummy values (10, 'test', 0, 'D');

1 row created.

SQL>  CREATE OR REPLACE PROCEDURE PRO_SEQ_ARRNGE(P_ID NUMBER) AS
  2  V_MOD DUMMY.MODULE_ID%TYPE;
  3  v_query1 varchar2(200);
  4  v_query2 varchar2(200);
  5  V_COUNT NUMBER;
  6  begin
  7  v_query1 := 'drop sequence unqid';
  8  v_query2 := 'create sequence unqid start with 1 increment by 1 minvalue 1';
  9  SELECT COUNT(*)
 10  INTO V_COUNT
 11  FROM USER_SEQUENCES
 12  WHERE SEQUENCE_NAME = 'UNQID';
 13
 14  IF V_COUNT = 0 THEN
 15  execute immediate v_query2;
 16  ELSE
 17  execute immediate v_query1;
 18  execute immediate v_query2;
 19  END IF;
 20
 21  SELECT distinct MODULE_ID INTO V_MOD FROM DUMMY WHERE ID = P_ID;
 22
 23  update dummy
 24  set P_order = 0, status = 'D'
 25  WHERE ID = P_ID
 26  and module_id = v_mod;
 27  --COMMIT;
 28
 29  execute immediate 'UPDATE DUMMY SET P_ORDER = UNQID.NEXTVAL WHERE MODULE_ID = V_MOD AND STATUS = ''A''';
 30  --COMMIT;
 31
 32  END PRO_SEQ_ARRNGE;
 33  /

Procedure created.

SQL> exec PRO_SEQ_ARRNGE(10);
BEGIN PRO_SEQ_ARRNGE(10); END;

*
ERROR at line 1:
ORA-00904: "V_MOD": invalid identifier
ORA-06512: at "SCOTT.PRO_SEQ_ARRNGE", line 29
ORA-06512: at line 1

The problem is that you can't refer to a local variable like V_MOD in a dynamic SQL statement. You'd need to use a bind variable

SQL> ed
Wrote file afiedt.buf

  1   CREATE OR REPLACE PROCEDURE PRO_SEQ_ARRNGE(P_ID NUMBER) AS
  2  V_MOD DUMMY.MODULE_ID%TYPE;
  3  v_query1 varchar2(200);
  4  v_query2 varchar2(200);
  5  V_COUNT NUMBER;
  6  begin
  7  v_query1 := 'drop sequence unqid';
  8  v_query2 := 'create sequence unqid start with 1 increment by 1 minvalue 1';
  9  SELECT COUNT(*)
 10  INTO V_COUNT
 11  FROM USER_SEQUENCES
 12  WHERE SEQUENCE_NAME = 'UNQID';
 13  IF V_COUNT = 0 THEN
 14  execute immediate v_query2;
 15  ELSE
 16  execute immediate v_query1;
 17  execute immediate v_query2;
 18  END IF;
 19  SELECT distinct MODULE_ID INTO V_MOD FROM DUMMY WHERE ID = P_ID;
 20  update dummy
 21  set P_order = 0, status = 'D'
 22  WHERE ID = P_ID
 23  and module_id = v_mod;
 24  --COMMIT;
 25  execute immediate 'UPDATE DUMMY SET P_ORDER = UNQID.NEXTVAL WHERE MODULE_ID = :1 AND STATUS = ''A'''
 26    using v_mod;
 27  --COMMIT;
 28* END PRO_SEQ_ARRNGE;
 29  /

Procedure created.

SQL> exec pro_seq_arrnge(10);

PL/SQL procedure successfully completed.

Of course, I'm not using the SYSTEM schema. You should really, really avoid SYS and SYSTEM-- things often work differently there than they do normally. I also join the other folks that have tried to help you in suggesting that creating a sequence dynamically in a procedure is a very poor idea and almost certainly indicates that you need to reconsider your design.

Justin

tinku981

I tried creating same on SCOTT user but getting same error. I can create sequence in SCOTT user. Therefore fail to understand which permissions are required to be granted?

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott

SQL> create table dummy (id number, module_id varchar2(20), p_order number, status varchar2(1));

Table created

SQL> insert into dummy values (10, 'test', 0, 'D');

1 row inserted

SQL>
SQL> CREATE OR REPLACE PROCEDURE PRO_SEQ_ARRNGE(P_ID NUMBER) AS
2 V_MOD DUMMY.MODULE_ID%TYPE;
3 v_query1 varchar2(200);
4 v_query2 varchar2(200);
5 V_COUNT NUMBER;
6 begin
7 v_query1 := 'drop sequence unqid';
8 v_query2 := 'create sequence unqid start with 1 increment by 1 minvalue 1';
9 SELECT COUNT(*)
10 INTO V_COUNT
11 FROM USER_SEQUENCES
12 WHERE SEQUENCE_NAME = 'UNQID';
13
14 IF V_COUNT = 0 THEN
15 execute immediate v_query2;
16 ELSE
17 execute immediate v_query1;
18 execute immediate v_query2;
19 END IF;
20
21 SELECT distinct MODULE_ID INTO V_MOD FROM DUMMY WHERE ID = P_ID;
22
23 update dummy
24 set P_order = 0, status = 'D'
25 WHERE ID = P_ID
26 and module_id = v_mod;
27 --COMMIT;
28
29 execute immediate 'UPDATE DUMMY SET P_ORDER = UNQID.NEXTVAL WHERE MODULE_ID = V_MOD AND STATUS = ''A''';
30 --COMMIT;
31
32 END PRO_SEQ_ARRNGE;
33
34 /

Procedure created

SQL> show errors
No errors for PROCEDURE SCOTT.PRO_SEQ_ARRNGE

SQL> exec PRO_SEQ_ARRNGE(10);

begin PRO_SEQ_ARRNGE(10); end;

ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_SEQ_ARRNGE", line 15
ORA-06512: at line 2

SQL> create sequence unqid start with 1 increment by 1 minvalue 1;

Sequence created

SQL> drop sequence unqid;

Sequence dropped

SQL>


Thanks in advance,
Tinku

tinku981

Further, adding to my above reply, I have following grants on SCOTT schema

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE ANY DIRECTORY
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

12 rows selected

Edited by: tinku981 on Jun 1, 2013 2:08 PM

Pleiadian

I'm not sure this is the case here, but privileges that are granted through a role do not apply to pl/sql.
Privileges must be granted to directly to the user