CODEWITHSUNDEEP
sqloracle-databaseora-00933
CodeRocker
CodeRocker

Reputation: 67

ORA-00933: SQL command not properly ended

I'm getting this error in Oracle:

ORA-00933: SQL command not properly ended
for
DROP SEQUENCE IF EXISTS ownername.seq_name;

Why am I seeing this?

Upvotes: 1

Views: 8411

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332521

As others mentioned, the IF EXISTS doesn't work on the DROP SEQUENCE command.

To test for the existence of a sequence, you need to check the appropriate view:

USER_SEQUENCES

SELECT * 
  FROM USER_SEQUENCES
 WHERE sequence_name = ?

DBA_SEQUENCES

SELECT * 
  FROM DBA_SEQUENCES
 WHERE sequence_name = ?

ALL_SEQUENCES

SELECT * 
  FROM ALL_SEQUENCES
 WHERE sequence_name = ?

Example:

BEGIN
   FOR i IN (SELECT sequence_name 
               FROM USER_SEQUENCES
              WHERE sequence_name = ?) 
   LOOP
     EXECUTE IMMEDIATE ('DROP SEQUENCE '|| i.sequence_name);
   END LOOP;
END;

Upvotes: 1

Chandu
Chandu

Reputation: 82893

Try this:

DECLARE
 iNum NUMBER DEFAULT 0;
BEGIN
  SELECT COUNT(1) 
      INTO  iNum 
   FROM ALL_SEQUENCES 
  WHERE SEQUENCE_OWNER='<OWNER_NAME>' 
       AND SEQUENCE_NAME = '<YOUR_SEQUENCE_NAME>';
 IF  iNum> 0 THEN
     EXECUTE IMMEDIATE 'DROP SEQUENCE <OWNER_NAME>.<YOUR_SEQUENCE_NAME>';
 END IF;
END;

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

the IF EXISTS clause doesn't exist in the DROP SEQUENCE command in Oracle.

You could use a PLSQL block to ignore the error:

SQL> DECLARE
  2     sequence_doesnt_exist EXCEPTION;
  3     PRAGMA EXCEPTION_INIT(sequence_doesnt_exist, -2289);
  4  BEGIN
  5     EXECUTE IMMEDIATE 'DROP SEQUENCE seq_name';
  6  EXCEPTION
  7     WHEN sequence_doesnt_exist THEN NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed

Upvotes: 4

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

The problem is "if exists" does not work in Oracle. Use:

drop sequence ownername.seq_name;

Upvotes: 1

Related Questions