SEQUENCE creation at runtime

vinayasagar
vinayasagar used Ask the Experts™
on
Hi
 I am trying to create a sequence at the run time in oracle. but i am getting an error while executing the code.
and one more thing if run the script as simple block it is working fine and creating the sequence but when i am trying to run as a procedure it is not working,
 here is the code.

------------------------------------
 declare
  LV_STARTNO number;
  v_sql varchar2(1000);
 begin  
 SELECT 'DROP SEQUENCE SEQ_UPDATE_SLNO'
  INTO v_sql
  FROM FO_PINS_SLNO;
  EXECUTE IMMEDIATE v_sql;
  SELECT SNO+1 INTO  LV_STARTNO FROM FO_PINS_SLNO;
  SELECT 'CREATE SEQUENCE SEQ_UPDATE_SLNO MINVALUE 0 START WITH '||LV_STARTNO||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM DUAL;
  EXECUTE IMMEDIATE v_sql;
 end;
 ----------------------------------
this is working fine.
----------------------------------

CREATE or REPLACE PROCEDURE UPDATE_SLNO AS
LV_STARTNO NUMBER;
v_sql varchar2(1000);
BEGIN  
  SELECT 'DROP SEQUENCE SEQ_UPDATE_SLNO'
  INTO v_sql
  FROM FO_PINS_SLNO;
  EXECUTE IMMEDIATE v_sql;
  SELECT SNO+1 INTO  LV_STARTNO FROM FO_PINS_SLNO;
  SELECT 'CREATE SEQUENCE SEQ_UPDATE_SLNO MINVALUE 0 START WITH '||LV_STARTNO||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM DUAL;
  EXECUTE IMMEDIATE v_sql;
  commit;
  end;
---------------
while executing i am getting insufficient privilege error.
Is there any problem in this code or should i need any privilege.

Thanks and regards
Vinay.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009
Commented:
grant drop sequence and grant create sequence to the user executing the procedure directly

the user probably has the grants given through a role now
Top Expert 2011
Commented:
- grant execute on the user that runs the procedure:
GRANT EXECUTE ON PROCEDURE <PROCEDURE_NAME> TO <USER>;

- if the procedure belongs to other user schema:
GRANT EXECUTE ON PROCEDURE <USER>.<PROCEDURE_NAME> TO <USER>;
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
"insufficient privilege error" -> this means that you don't have enough privileges to create a procedure, contact your dba to fix it.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
HI
  I am able create the procedure while executing i am getting that error..
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Ok, is the procedure compiled without warnings (check in SQL Developer or Toad)? Can you verify that the user who executes the procedure has got the rights as Geert_Gruwez and OP_Zaharin are suggesting? Can you show how you are executing the procecure?
Information Technology Specialist
Commented:
CREATE or REPLACE PROCEDURE UPDATE_SLNO
AUTHID CURRENT_USER AS
LV_STARTNO NUMBER;
v_sql varchar2(1000);
BEGIN  
  SELECT SNO+1 INTO  LV_STARTNO FROM FO_PINS_SLNO;
  EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_UPDATE_SLNO';
  v_sql := 'CREATE SEQUENCE SEQ_UPDATE_SLNO MINVALUE 0 START WITH '||LV_STARTNO||' INCREMENT BY 1 CACHE 20';
  EXECUTE IMMEDIATE v_sql;
END;
Mark GeerlingsDatabase Administrator
Commented:
Usually it is best in Oracle to not drop and create sequences dynamically. (And if you do, you certainly need the privileges that others have mentioned.) Are you sure that this is the best way to solve the business problem you have?
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
No points for me :(

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial