Home » SQL & PL/SQL » SQL & PL/SQL » Reset Sequence
Reset Sequence [message #187570] Mon, 14 August 2006 09:06 Go to next message
lizkorah
Messages: 2
Registered: August 2006
Junior Member
I am trying to recreate the sequence of a table using a stored procedure in Oracle 9i.

The procedure compiled without any errors. However, when I try to execute this in TOAD, I keep getting the errors described below.

Procedure:
CREATE OR REPLACE PROCEDURE Reset_EXBL_Sequence IS
v_Exbl_Num NUMBER;
BEGIN
-- Find the highest EXBL_NUM value in the EXBL table.
SELECT MAX(exbl_num) INTO v_Exbl_Num FROM EXBL;

-- Set the value for the next EXBL_NUM
v_Exbl_Num := v_Exbl_Num + 1;

-- Drop the sequence EXBL_SEQ
EXECUTE IMMEDIATE 'DROP SEQUENCE exbl_seq';

-- Recreate the Sequence EXBL_SEQ starting with the correct v_Exbl_Num.
EXECUTE IMMEDIATE 'CREATE SEQUENCE exbl_seq START WITH '||v_Exbl_Num||';';

END Reset_EXBL_Sequence;

Execution:
EXEC RESET_EXBL_SEQUENCE;

Errors:
ORA-00911: invalid character
ORA-06512: at "EK.RESET_EXBL_SEQUENCE", line 14
ORA-06512: at line 1



Re: Reset Sequence [message #187571 is a reply to message #187570] Mon, 14 August 2006 09:09 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
change
EXECUTE IMMEDIATE 'CREATE SEQUENCE exbl_seq START WITH '||v_Exbl_Num||';';

to EXECUTE IMMEDIATE 'CREATE SEQUENCE exbl_seq START WITH '||v_Exbl_Num;
Re: Reset Sequence [message #187583 is a reply to message #187571] Mon, 14 August 2006 09:36 Go to previous messageGo to next message
lizkorah
Messages: 2
Registered: August 2006
Junior Member
Thanks ! That worked perfectly !
Re: Reset Sequence [message #187596 is a reply to message #187583] Mon, 14 August 2006 10:43 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
When you drop and recreate a sequence, all dependent objects become invalid, so it is better to reset it. Here is a link to a thread on Tom Kyte's site that includes a procedure by him to dynamically reset a sequence by altering it, rather than dropping and recreating it:


http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:951269671592

Previous Topic: select statement, default value
Next Topic: insert approach
Goto Forum:
  


Current Time: Tue Dec 06 14:02:59 CST 2016

Total time taken to generate the page: 0.15081 seconds