| Problem while creating SEQUENCE from PL/SQL [message #39707] |
Fri, 02 August 2002 13:52  |
Swamy
Messages: 78
Registered: June 2002
|
Member |
|
|
I wrote a procedure as shown below.
CREATE OR REPLACE PROCEDURE refresh_sequence (schema_name IN VARCHAR2, sequence_name IN VARCHAR2)
IS
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'DROP SEQUENCE ' ||schema_name
|| '.' || sequence_name;
EXECUTE IMMEDIATE sql_stmt;
sql_stmt := 'CREATE SEQUENCE ' ||schema_name || '.' || sequence_name || 'INCREMENT BY 1 START WITH 1 MINVALUE 1';
EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'Exception Raised: ' || SQLCODE || ' - ' ||SQLERRM);
END;
I am trying to drop the existing SEQUENCE and then to create the same for getting start values. The procedure is successfully droping the sequence, but giving errors to create. It is giving the following error:
ERROR at line 1:
ORA-20000: Exception Raised: -933 - ORA-00933: SQL command not properly ended
ORA-06512: at "EPHRADEV.REFRESH_SEQUENCE", line 12
ORA-06512: at line 1
Can any one help me what I am doing wrong. I appreciate in advance for your help.
Is there any other solution to refresh a sequence programmatically.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
