troubleshooting Question

SEQUENCE creation at runtime

Avatar of vinayasagar
vinayasagar asked on
Oracle Database
8 Comments4 Solutions425 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
awking00
Information Technology Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros