Sql error ora 00933 неверное завершение sql предложения 00933 00000 sql command not properly ended - Исправление ошибок и поиск оптимальных решений проблем

Are you getting the ORA-00933: SQL command not properly ended error? Learn what causes it and how to resolve it in this article.

This error is 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

ORA-00933 Solution

The solution to the ORA-00933 error is to update your query to remove the clause that’s causing the issue. This would depend on the type of query being run.

Let’s take a look at some example solutions.

INSERT Statement

If you’re getting an “ORA-00933 sql command not properly ended” on INSERT, then it could be because:

You have a JOIN keyword (such as INNER JOIN, LEFT JOIN) in the query.
You have an ORDER BY in the query.

You might have a query that looks like this:

INSERT INTO student (student_id, first_name, last_name)
VALUES (20, 'Jack', 'Wheeler')
ORDER BY student_id;

This statement fails because the INSERT statement does not support ORDER BY. When you insert data, you don’t need to specify an order. The data is inserted into the table anyway, and the ORDER BY is only used for SELECT queries.

So, to correct the statement and stop the error, remove the ORDER BY:

INSERT INTO student (student_id, first_name, last_name)
VALUES (20, 'Jack', 'Wheeler');

Refer to my guide on the SQL INSERT statement here for more information.

UPDATE Statement

If you’re getting the “ORA-00933 sql command not properly ended” on UPDATE, then your query might look like this:

UPDATE student
SET student.fees_paid = payment.amount
INNER JOIN payment ON student.student_id = payment.student_id;

You can’t use a JOIN clause in an UPDATE statement. To update a value like this, include the JOIN logic in a subquery:

UPDATE student
SET student.fees_paid = (
  SELECT amount
  FROM payment
  WHERE student.student_id = payment.student_id
);

This statement should now run without errors.

Read my guide on the SQL UPDATE statement for more information.

DELETE Statement

If you’re getting the “ORA-00933 sql command not properly ended” on DELETE, then your query might look like this:

DELETE FROM student
WHERE last_name = 'Smith'
ORDER BY student_id;

The error appears because the ORDER BY does not belong in a DELETE statement. The ORDER BY is only for ordering data returned by a SELECT statement, and serves no purpose in a DELETE statement.

So, change your query to remove the ORDER BY clause:

DELETE FROM student
WHERE last_name = 'Smith';

You can refer to my guide on the SQL DELETE statement for more information.

SELECT Statement

Are you getting an “ORA-00933 sql command not properly ended” in a SELECT query?

Well, the SELECT query can support joins and ORDER BY, so what could be causing it?

There can be several reasons for this:

Your SELECT statement is using UNION or UNION ALL, and you have an ORDER BY at any point except the end of the query. You can only have an ORDER BY at the end of the query, not within each UNION.
You have forgotten a comma in between tables when selecting them.
You’re running Oracle 8i and trying to use INNER JOIN keywords (or similar join keywords). These were implemented in Oracle 9i.

The exact solution will depend on your SELECT query, but here are a few things you can check:

Check that you have the right clauses for your query and are in the right place (e.g. not missing a FROM clause).
Check that you’re not missing a comma anywhere, such as in the SELECT clause or the FROM clause.
Check that you’re not missing a bracket anywhere. This can be made easier with SQL Developer’s matching bracket highlighting or formatting the SQL to see if something is missing. Other IDEs have similar features.

So, that’s how you resolve the ORA-00933 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Источник

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 ;

Источник

Prior to diving into this error, it’s important we discuss how Oracle processes queries logically and in what order.

Oracle has a couple of rules as to how it executes each query in a specific processing order. Oracle processes a query as below:

Gathers rows based on JOINS and the FROM clause determining the objects the query is running against
Filters out rows based on the WHERE clause
Groups said rows with the GROUP BY clause
Filters groups based on the HAVING clause
Orders the results based on the ORDER BY clause
NOTE: the ORDER BY clause must contain values from the GROUP BY clause or a group function
Limits the rows based on the LIMIT clause to only return a specific amount of rows

Now that we understand the overall query processing order, the “ORA-00933: SQL command not properly ended” error is caused by a clause in the code that doesn’t fall within the bounds and rules of the order. The error is commonly caused by the following situations.

An INSERT statement with an ORDER BY clause. Oracle doesn’t support the inserting of rows in a certain order via the ORDER BY clause — that is done during sorting operations of SELECT queries. To fix this, simply remove the ORDER BY clause. Note: there are cases where you would want the data ordered so you can insert the clause in a subquery, which is common with CREATE VIEW statements.
A DELETE statement with an INNER JOIN or ORDER BY clause. Similar to the previous situation, Oracle doesn’t allow ordering rows in a particular fashion to then be deleted. You could resolve the query containing joins by converting the INNER JOIN to WHERE EXISTS (or a subquery). For example:

From:
DELETE FROM t1
INNER JOIN t2 on t1.id = t2.id
WHERE t2.value2 = '...';

To:
DELETE FROM t1
WHERE id in (
    SELECT id
    FROM t2
    WHERE t2.value2 = '...');

An UPDATE statement with an INNER JOIN. Similar to the previous situation, Oracle doesn’t allow a join clause on UPDATEs. To resolve this, convert the join to a subquery. For example:

From:
UPDATE t1
SET t1.value1 = t2.value2
INNER JOIN t2 ON t1.id = t2.id;
 
To:
UPDATE t1
SET t1.value1 = (
  SELECT value2
  FROM t2
  WHERE t1.id = t2.id);

Источник

Содержание

ORA-00933: SQL command not properly ended
Submit a Comment Cancel reply
ORA-00933: SQL command not properly ended | ORA-00933 Resolution
ORA-00933 : SQL command not properly ended :
Error Cause and Resolution :
ORA-00933: SQL command not properly ended | ORA-00933 Resolution
ORA-00933 : SQL command not properly ended :
Error Cause and Resolution :
ORA-00933:SQL command not properly ended with CASE..END expression in statement #7701
Comments
System information:
Connection specification:
Describe the problem you’re observing:
Steps to reproduce, if exist:
Include any warning/errors/backtraces from the logs

ORA-00933: SQL command not properly ended

Prior to diving into this error, it’s important we discuss how Oracle processes queries logically and in what order.

Oracle has a couple of rules as to how it executes each query in a specific processing order. Oracle processes a query as below:

Gathers rows based on JOINS and the FROM clause determining the objects the query is running against
Filters out rows based on the WHERE clause
Groups said rows with the GROUP BY clause
Filters groups based on the HAVING clause
Orders the results based on the ORDER BY clause
NOTE: the ORDER BY clause must contain values from the GROUP BY clause or a group function
Limits the rows based on the LIMIT clause to only return a specific amount of rows

An INSERT statement with an ORDER BY clause. Oracle doesn’t support the inserting of rows in a certain order via the ORDER BY clause — that is done during sorting operations of SELECT queries. To fix this, simply remove the ORDER BY clause. Note: there are cases where you would want the data ordered so you can insert the clause in a subquery, which is common with CREATE VIEW statements.
A DELETE statement with an INNER JOIN or ORDER BY clause. Similar to the previous situation, Oracle doesn’t allow ordering rows in a particular fashion to then be deleted. You could resolve the query containing joins by converting the INNER JOIN to WHERE EXISTS (or a subquery). For example:

An UPDATE statement with an INNER JOIN. Similar to the previous situation, Oracle doesn’t allow a join clause on UPDATEs. To resolve this, convert the join to a subquery. For example:

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Источник

ORA-00933: SQL command not properly ended | ORA-00933 Resolution

ORA-00933 : SQL command not properly ended :

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain the most common error, which has been shared, on google 10 k times per month. ORA-00933 is straightforward error might come because of inappropriate use of any SQL clause.

Error Cause and Resolution :

ORA-00933 error is very common error, which will come because of using the SQL clause in inappropriate syntax. There is no another root cause of this error. User might check the clause at appropriate place to resolve this issue. I will explain different scenarios of this error

Use of Order by / Group by Clause in Insert Statement :

This error will come because of the improper use of ‘Order by’ clause in insert statement. Kindly try to implement following scenario :

(Roll_no NUMBER(10),NAME VARCHAR2(30));

values(1,’Amit’) order by roll_no;

Resolution of error :

Kindly remove the order by clause from select statement.

User needs to check the error the syntax of inappropriate clauses in SQL.The clauses might be either ‘Order by’ or ‘Group by’.So the root cause of this error is placing of clause at inappropriate place.

Use of Order by / Group by Clause in delete Statement :

In above example I have given example of order by clause. In this example I will try to produce this error with group by clause. User can not be used the group by clause with delete statement. This is most common syntax error.

delete from Test_Error group by Roll_no;

SQL Error: ORA-00933: SQL command not properly ended

00000 – “SQL command not properly ended”

Solution of the error :

Remove group by Roll_no clause. The statement will be:

3.Improper use of joins in update statement :

The improper use of join in update statement one of the main cause of this error.

Example :

UPDATE S_SRV_REQ set SR_NUM =’1000’

FROM S_SRV_REQ_XM SRVX

JOIN S_SRV_REQ sr ON SRVX.ROW_ID = sr.PAR_ROW_ID;

In above example user is trying to update the SR_NUM but user is using the join condition between two tables. So user needs to change the query as below :

Источник

ORA-00933: SQL command not properly ended | ORA-00933 Resolution

ORA-00933 : SQL command not properly ended :

Error Cause and Resolution :

Use of Order by / Group by Clause in Insert Statement :

This error will come because of the improper use of ‘Order by’ clause in insert statement. Kindly try to implement following scenario :

(Roll_no NUMBER(10),NAME VARCHAR2(30));

values(1,’Amit’) order by roll_no;

Resolution of error :

Kindly remove the order by clause from select statement.

User needs to check the error the syntax of inappropriate clauses in SQL.The clauses might be either ‘Order by’ or ‘Group by’.So the root cause of this error is placing of clause at inappropriate place.

Use of Order by / Group by Clause in delete Statement :

delete from Test_Error group by Roll_no;

SQL Error: ORA-00933: SQL command not properly ended

00000 – “SQL command not properly ended”

Solution of the error :

Remove group by Roll_no clause. The statement will be:

3.Improper use of joins in update statement :

The improper use of join in update statement one of the main cause of this error.

Example :

UPDATE S_SRV_REQ set SR_NUM =’1000’

FROM S_SRV_REQ_XM SRVX

JOIN S_SRV_REQ sr ON SRVX.ROW_ID = sr.PAR_ROW_ID;

In above example user is trying to update the SR_NUM but user is using the join condition between two tables. So user needs to change the query as below :

Источник

ORA-00933:SQL command not properly ended with CASE..END expression in statement #7701

First of all, thank you for this great, great, great tool. The fact I can use the same tool to connect to MS SQL Server and Oracle, and that I can use templates, is simply magic!

System information:

Windows 10 Pro
DBeaver version 6.3.3.202001140839
No additional extensions

Connection specification:

Database name and version: Oracle 12c
Driver name: ojdbc7.jar
Do you use tunnels or proxies (SSH, SOCKS, etc)? No: everything local on my machine

Describe the problem you’re observing:

Running the request:

SELECT
pct_id,
CASE
WHEN bl_id IS NOT NULL
THEN bl_id
ELSE ‘bl was null’
END AS bl_truc,
user_name
FROM rmpct;

ORA-00933:SQL command not properly ended

This is «because» of the CASE..END expression; without it, it does work.

(I’ve also noted that, for Oracle, the CASE..END couldn’t be the last expression in the SELECT list: sometimes, I do have to add a string before the FROM for the request to be correctly parsed.

Steps to reproduce, if exist:

Put the above request in the editor, and run it with Alt-x.

Include any warning/errors/backtraces from the logs

Error ORA-00933:SQL command not properly ended

The text was updated successfully, but these errors were encountered:

Источник