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