Avatar of vinayasagar
vinayasagar
 asked on

SEQUENCE creation at runtime

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.
Oracle Database

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
SOLUTION
Geert G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
OP_Zaharin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gerwin Jansen

"insufficient privilege error" -> this means that you don't have enough privileges to create a procedure, contact your dba to fix it.
vinayasagar

ASKER
HI
  I am able create the procedure while executing i am getting that error..
Gerwin Jansen

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gerwin Jansen

No points for me :(