Link to home
Start Free TrialLog in
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.
SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"insufficient privilege error" -> this means that you don't have enough privileges to create a procedure, contact your dba to fix it.
Avatar of vinayasagar
vinayasagar

ASKER

HI
  I am able create the procedure while executing i am getting that error..
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No points for me :(