Forum Stats

  • 3,751,235 Users
  • 2,250,335 Discussions
  • 7,867,351 Comments

Discussions

Using dynamic query to create sequence

tinku981
tinku981 Member Posts: 72
edited Jun 1, 2013 11:04AM in SQL & PL/SQL
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
Tagged:
«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    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
    tinku981 Member Posts: 72
    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
    sb92075 Member Posts: 42,196 Blue Diamond
    ORA-06512: at "SYSTEM.PRO_SEQ_ARRNGE", line 15
    You should never use SYS or SYSTEM for application activities.
  • Give the user the proper privileges.
  • tinku981
    tinku981 Member Posts: 72
    The required rights are there!

    Did you tried it?
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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
    JustinCave Member Posts: 30,293 Gold Crown
    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
    tinku981 Member Posts: 72
    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
    tinku981 Member Posts: 72
    edited Jun 1, 2013 4:38AM
    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
    Pleiadian Member Posts: 514 Silver Badge
    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
This discussion has been closed.