Код ошибки 00933

ORA-00933: SQL command not properly ended error occurs when an Oracle SQL command is ended with an inappropriate clause. The DML commands INSERT, UPDATE, DELETE, and SELECT should use oracle’s syntax. The error is thrown when a clause is added that does not normally come within the command of a SQL query. An SQL statement with a clause that isn’t allowed for that statement causes this error. The error SQL Error: ORA-00933: SQL command not properly ended may be fixed by updating your query and removing the clause that is creating the problem. This would be determined by the query type.

This error is caused by a SQL statement that has a clause that isn’t permitted in that statement. The query contains a JOIN keyword, such as INNER JOIN or LEFT JOIN. The query contains an ORDER BY that is not permitted in the query. In your query, you have a where clause that isn’t allowed. You may need to update the sql query using various ways such as sub queries or correlated queries to achieve the same functional results. To resolve the problem, eliminate any clauses that aren’t related to the sql query.

The Problem

If you add an incorrect clause to a query like INSERT, UPDATE, DELETE, or SELECT, the sql query will not be able to add the clause. The Oracle query will fail because it was unable to process the clause that is unrelated to the sql query. To get the same functionality, the clause should be deleted from the sql query or the query should be redone using an alternative technique.

update employee set deptid=1 orderby name;

Error

Error starting at line : 4 in command -
update employee set deptid=1 orderby name
Error at Command Line : 4 Column : 30
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Solution 1

The INNER JOIN, OUTER JOIN, WHERE clause, or ORDER BY clause are not allowed in the INSERT statement. If any of the above clauses are included in the INSERT statement, the sql query will fail because it will encounter the irrelevant clause linked to the insert statement. To fix the error SQL Error: ORA-00933: SQL command not properly ended, delete the above clause from the INSERT statement.

Error 1

insert into employee values (2,'test',1) order by name;

Solution 1

insert into employee values (2,'test',1);

Error 2

insert into employee values (2,'test',1) where name ='sample';

Solution 2

insert into employee values (2,'test',1);

Error 3

insert into employee values (2,'test',1) join dept on dept.deptid=employee.deptid;

Solution 3

insert into employee values (2,'test',1);

Solution 2

If the update statement contains the irrelevant clauses such as ORDER BY, INNER JOIN, OUTTER JOIN the sql query will throw the error. The ORDER BY clause or joins should be removed to resolve the error SQL Error: ORA-00933: SQL command not properly ended.

Error 1

update employee set deptid=1 orderby name;

Solution 1

update employee set deptid=1;

Error 2

update employee
set employee.name = manager.name
inner join manager ON employee.managerid = manager.empid;

Solution 2

update employee set employee.name = (
  select name from manager
  where employee.managerid = manager.empid
);

Solution 3

If the DELETE statement includes an ORDER BY clause or an INNER JOIN, OUTER JOIN, the delete statement will fail. The table name and where condition for retrieving the record to delete should be included in the DELETE statement. If any additional clause is introduced to the delete statement, Oracle will thrown an error SQL Error: ORA-00933: SQL command not properly ended

Error 1

delete from employee where deptid=1 orderby name;

Solution 1

delete from employee where deptid=1;

Error 2

delete from employee
where employee.name = manager.name
inner join manager ON employee.managerid = manager.empid;

Solution 2

delete from employee where employee.name = (
  select name from manager
  where employee.managerid = manager.empid
);

Solution 4

If the SELECT query contains two or more tables, the tables are connected by using a comma as a separator and appending the table name. The error will be thrown if a comma is missing between table names. In the select query, a comma should be used between table names.

Error

select * from employee a employee b;

Solution

select * from employee a, employee b;

Solution 5

The select query includes the WHERE clause, ORDER BY clause, GROUP BY clause, and HAVING clause. The clauses must be placed in the proper order. If the clauses are added in a different order than expected, an error SQL Error: ORA-00933: SQL command not properly ended will be thrown.

Error

select * from employee b order by b.name where b.name='test';

Solution

select * from employee b where b.name='test' order by b.name ;

Summary

Bitbucket server startup failed with the below error:

ORA-00933: sql command not properly ended

Diagnosis

Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE ADMIN.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'tti-bbk001-cl.bps.private (28.12.11.57)', LOCKGRANTED = TO_TIMESTAMP('2021-08-26 04:03:36.019', 'YYYY-MM-DD HH24:MI:SS.FF') WHERE ID = 1 AND LOCKED = 0: ORA-00933: SQL command not properly ended

The lock is established during start-up, and then released once all changes to the database are done (all DDL, or structural changes). The node while joining the cluster is setting the lock into databasechangeloglock for the time required to verify the schema usually for few seconds or maybe a minute, but not more than that, then the lock is released and you would pretty much never see the lock again. There are cases where table databasechangeloglock has not been updated with the release lock information as described in KB Could not acquire change log lock.
It’s the Liquibase that uses the DATABASECHANGELOGLOCK table to ensure only one instance of Liquibase is running at one time. So it prevents the racing condition during the startup of the nodes in the cluster or during the upgrade procedure when the lock stays during the whole schema upgrade procedure.

Cause

The message ORA-00933: sql command not properly ended. This error is usually caused by an SQL statement with a clause that is not allowed for that statement. Some examples that might cause this error are:

• An INSERT statement with an ORDER BY clause or an INNER JOIN
• A DELETE statement with an INNER JOIN or ORDER BY clause
• An UPDATE statement with an INNER JOIN
• If the SQL syntax is incorrect.

Solution

Bitbucket Server needs an exclusive lock on the DATABASECHANGELOGLOCK table in order to start successfully. This table needs to be updated with the release lock information.

UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

Please refer to our KB article: 

• Bitbucket Server Does Not Start — Could not acquire change log lock

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 broadly vary. Here’re some cases of the error.

1. ALTER TABLESPACE
2. Create Table as Select (CTAS)
3. Substitution Variable

ALTER TABLESPACE

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;

We solve it.

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

Create Table as Select (CTAS)

Tried to backup a table to a another 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

Solution

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;

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

It works!

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