Skip to content

How to Resolve ORA-00933: SQL command not properly ended

  • by

ORA-00933

ORA-00933 means that you use an unexpected word or phrase at the position where SQL parser knows what qualified keyword should be. Use cases that throw ORA-00933 may vary. Here're some cases of the error.

  1. AUTOEXTEND ON
  2. Create Table as Select (CTAS)
  3. Substitution Variable

1. AUTOEXTEND ON

Tried to add a tempfile to a tablespace, but it failed with ORA-00933.

SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited;
alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m auto extend on next 10m maxsize unlimited
                                                                                *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Solution

Actually, the keyword is AUTOEXTEND, not AUTO EXTEND. We should correct the statement like this:

SQL> alter tablespace temp add tempfile '+DATA/ORCLCDB/ERPAPP2A/TEMP32.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

We solve it.

To correctly operate temporary tablespace, you may refer to the post: How to Alter Temporary Tablespace.

2. Create Table as Select (CTAS)

Tried to backup a table to a specific tablespace by CTAS, but it failed with ORA-00933.

SQL> create table hr.employees_bak as select * from hr.employees tablespace example;
create table hr.employees_bak as select * from hr.employees tablespace example
                                                                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended

This is because the TABLESPACE clause cannot be at the last, it should stick with CREATE TABLE clause

SQL> create table hr.employees_bak tablespace example as select * from hr.employees;

Table created.

3. Substitution Variable

There're hundreds of tables like pro001env, pro002env, ... and so on.

Let's see one of these tables.

SQL> desc pro220env
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

When we want to use a substitution variable to execute the statement, it failed with ORA-00933.

SQL> select * from pro'&num'env;
Enter value for num: 220
old   1: select * from pro'&num'env
new   1: select * from pro'220'env
select * from pro'220'env
                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended

As you can see, we use single quotes to isolate the variable, but it's not working.

Solution

To use substitution variable to concatenate a string, you need a period (.) to separate the substitution variable from rest of characters.

SQL> select * from pro&num.env;
Enter value for num: 220
old   1: select * from pro&num.env
new   1: select * from pro220env

        C1
----------
         1
         2
         3

It works!

By the way, the use case of substitution variables is different from the scenarios of using bind variables.

Leave a Reply

Your email address will not be published. Required fields are marked *