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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"insufficient privilege error" -> this means that you don't have enough privileges to create a procedure, contact your dba to fix it.
ASKER
HI
I am able create the procedure while executing i am getting that error..
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points for me :(