Ошибка oracle ora 00933

ORA-00933: команда SQL не закончена надлежащим образом

Причина:

SQL оператор заканчивается несоответствующим выражением. Например предложение ORDER BY может быть включено в оператор CREATE VIEW или INSERT. ORDER BY не может быть использовано для создания упорядоченного VIEW или для INSERT в определенном порядке. Вы будете всегда получать несоответствующее SQL окончание, если вы используете условие IN только с одним аргументом (например ,IN (X) ). Условие IN должно иметь два или более аргументов.

Действие:

Проверьте синтаксис оператора и удалите неподходящие компоненты. У вас всегда есть возможность продублировать SQL предложение, другим оператором. Например, если вы хотите упорядочить строки VIEW, делайте это когда вы запрашиваете VIEW, но не когда вы его создаете. Ошибки также случаются в применениях SQL*Forms, если соединительная линия идентична. Проверьте на идентичность линии и удалите эти пространства (места).

I have the following code:

begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
      dbms_random.string('U',5),
      trunc(dbms_random.value(0000,9999)),
      prod_id from dba_xy.product 
              prod_name from dba_xy.product;        
end loop;

end;

When I run it, oracle gives me the following error message:

prod_name from dba_xy.product;
                        *

ERROR at line 8:
ORA-06550: line 8, column 29:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored

What I’m trying to do is link the existing prod_id and prod_name with new data inserted into the despatch table. I have set prod_name as a unique key in the product table and prod_id as the primary key and have set both as foreign key constraints in the despatch table. I need to include the prod_name into the despatch table to allow readers of the table to have more understanding of what prod_name needs to be found etc, rather than just giving the prod_id which will make no sense to them at all. But maybe I was thinking that I don’t need prod_id in the despatch table.
Please help.

After dropping the prod_id column from the despatch table, i altered my code:

begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
      dbms_random.string('U',5),
      trunc(dbms_random.value(0000,9999)),
              prod_name from dba_xy.product;        
end loop;

end;
/

and the following error message came up about the unique constraint:
begin
*
ERROR at line 1:
ORA-00001: unique constraint (DBA_XY.PROD_NAME_UC) violated
ORA-06512: at line 3

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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

First, separate you queries with a semicolon and fix your SET conditions:

CREATE VIEW cambiodatos AS 
SELECT 
    a.last_name||','||a.first_name AS "Nombre", 
    a.salary AS "Salario", 
    b.name AS "Nombre Departamento", 
    c.name AS "Nombre de Region"
FROM 
    s_emp a, s_dept b, s_region c
WHERE 
    a.dept_id = b.id AND b.region_id = c.id;

UPDATE 
     cambiodatos 
SET 
     name = 'North America'
WHERE 
     last_name = 'Biri'
     AND first_name = 'Ben'

That’s the reason of your error ORA-00933

Second, your UPDATE statement will fail, as the view you created does not contain field name.

This query will compile:

UPDATE 
     cambiodatos 
SET 
     "Nombre de Region" = 'North America'
WHERE 
     "Nombre" = 'Biri, Ben'

, but most probably will fail as s_region is not key-preserved in this view.

To update, use this instead:

MERGE
INTO    s_region c
USING   (
        SELECT  b.region_id
        FROM    s_emp a, s_dept b
        WHERE   a.last_name || ',' || a.first_name = 'Biri, Ben'
                AND b.id = a.dept_id
        ) q
ON      c.id = q.region_id
WHEN MATCHED THEN
UPDATE
SET     c.name = 'North America'

The ORA-00933 error is a common issue in Oracle databases and occurs when a SQL command is not properly ended. This can happen due to various reasons, such as missing a closing parenthesis, using a reserved keyword as an identifier without proper quotes, or simply making a typo in the SQL statement. It is important to understand the root cause of the error in order to resolve it effectively.

Method 1: Check for Properly Ended SQL Statements

To fix the SQL Error: ORA-00933: SQL command not properly ended, you can use the «Check for Properly Ended SQL Statements» method. This involves checking your SQL statements to ensure that they are properly ended with a semicolon (;).

Here is an example of a SQL statement that is not properly ended:

SELECT * FROM customers WHERE customer_id = 123

To fix this, simply add a semicolon at the end of the statement:

SELECT * FROM customers WHERE customer_id = 123;

You can also check for properly ended SQL statements using a code editor or an IDE that supports SQL syntax highlighting. In most cases, the editor will highlight any SQL statements that are not properly ended, making it easy to spot and fix the error.

Here is an example of a SQL statement that is not properly ended in an IDE:

SELECT * FROM orders WHERE order_date > '2021-01-01'

The IDE highlights the error, making it easy to spot:

SELECT * FROM orders WHERE order_date > '2021-01-01';

In conclusion, to fix the SQL Error: ORA-00933: SQL command not properly ended, always ensure that your SQL statements are properly ended with a semicolon (;). You can use a code editor or an IDE that supports SQL syntax highlighting to check for properly ended SQL statements.

Method 2: Use Proper Quotes for Reserved Keywords

One way to fix the SQL Error: ORA-00933: SQL command not properly ended is to use proper quotes for reserved keywords. This error occurs when there is a syntax error in the SQL query, such as a missing quote or a misplaced keyword. To fix this error, you can use quotes around any reserved keywords in the query.

For example, if you have a query that includes a column named «order», which is a reserved keyword in SQL, you can use quotes to indicate that it is a column name and not a keyword:

SELECT "order" FROM orders;

You can also use quotes around table names, aliases, and other identifiers to avoid syntax errors:

SELECT * FROM "orders" WHERE "status" = 'pending';

Note that the type of quotes used can vary depending on the database system. For example, Oracle databases typically use double quotes for identifiers, while MySQL uses backticks. Be sure to check the documentation for your specific database system to determine the correct syntax.

In summary, to fix the SQL Error: ORA-00933: SQL command not properly ended, use proper quotes for reserved keywords in your SQL query. This can help avoid syntax errors and ensure that your query is executed correctly.

Method 3: Ensure Proper Parenthesis Usage

To fix the SQL Error: ORA-00933: SQL command not properly ended using Ensure Proper Parenthesis Usage, you need to ensure that all opening parentheses have a corresponding closing parentheses, and vice versa. Here are some examples:

Example 1: Incorrect usage of parentheses

SELECT * FROM orders WHERE order_date > '2021-01-01' AND (status = 'pending';

To fix this error, you need to add a closing parenthesis at the end of the statement:

SELECT * FROM orders WHERE order_date > '2021-01-01' AND (status = 'pending');

Example 2: Incorrect usage of parentheses with multiple conditions

SELECT * FROM orders WHERE (order_date > '2021-01-01' AND status = 'pending';

To fix this error, you need to add a closing parenthesis for each opening parenthesis:

SELECT * FROM orders WHERE (order_date > '2021-01-01') AND (status = 'pending');

Example 3: Incorrect usage of parentheses with subqueries

SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 123;

To fix this error, you need to add a closing parenthesis at the end of the subquery:

SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 123);

By ensuring proper usage of parentheses, you can avoid the ORA-00933 error and ensure that your SQL commands are properly executed.

Method 4: Verify Syntax of SQL Command

To fix the SQL Error: ORA-00933: SQL command not properly ended, you can use the «Verify Syntax of SQL Command» method. This method involves checking the syntax of your SQL command to ensure that it is written correctly. Here are the steps to follow:

  1. Open your SQL command in a SQL editor.
  2. Highlight the SQL command that is causing the error.
  3. Right-click on the highlighted text and select «Verify Syntax of SQL Command» from the context menu.
  4. The SQL editor will check the syntax of your command and highlight any errors.
  5. Review the errors and make the necessary corrections to your SQL command.

Here are some examples of SQL commands that may cause the ORA-00933 error:

Example 1: Missing semicolon

SELECT *
FROM employees
WHERE department = 'Sales'

To fix this error, add a semicolon at the end of the SQL command:

SELECT *
FROM employees
WHERE department = 'Sales';

Example 2: Incorrect use of parentheses

SELECT *
FROM employees
WHERE department = 'Sales'
AND (hire_date > '01-JAN-2020' OR salary > 50000;

To fix this error, add a closing parenthesis at the end of the SQL command:

SELECT *
FROM employees
WHERE department = 'Sales'
AND (hire_date > '01-JAN-2020' OR salary > 50000);

Example 3: Misspelled keyword

SELECT *
FROM employees
WHERE depatment = 'Sales';

To fix this error, correct the spelling of the keyword:

SELECT *
FROM employees
WHERE department = 'Sales';

Method 5: Check for Additional Errors in SQL Statement

To fix the SQL Error: ORA-00933: SQL command not properly ended, you can check for additional errors in the SQL statement. Here are the steps to do it:

  1. Review the SQL statement and make sure all keywords and syntax are correct.
  2. Check for any missing or extra punctuation marks such as semicolons, commas, and parentheses.
  3. Verify that all column and table names are spelled correctly and that they exist in the database.
  4. Make sure that all values and data types are correct and match the database schema.

Here are some code examples:

Example 1:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1'

This SQL statement is missing a semicolon at the end. To fix it, add a semicolon:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1';

Example 2:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1'
AND column2 = 'value2'

This SQL statement is missing a closing parenthesis. To fix it, add a closing parenthesis:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1'
AND column2 = 'value2';

Example 3:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1'
AND column2 = value2

This SQL statement has a data type mismatch error. To fix it, enclose the value2 in quotes:

SELECT column1, column2 FROM table1 WHERE column1 = 'value1'
AND column2 = 'value2';

By following these steps and checking for additional errors in the SQL statement, you can fix the SQL Error: ORA-00933: SQL command not properly ended.

  • Ошибка ora 12542
  • Ошибка oracle ora 06550
  • Ошибка ora 04043
  • Ошибка ora 02291 integrity constraint
  • Ошибка oracle ora 01017