ORA-00933: SQL command not properly ended

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 ;



Leave a Reply