FB

How To Resolve ORA-00933 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.

ORA-00933ORA-00933: sql command not properly ended

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!

14 thoughts on “How To Resolve ORA-00933 SQL Command Not Properly Ended”

  1. Ben, I am getting ORA-00933, so I’ve been looking for help online. Most all the answers I’ve seen assume that the query is performing things that mine does not, like UPDATE, DELETE, etc., or some JOINs or UNIONs. Mine is just a select from 4 input files which asks for COUNT(*).
    — Device validation

    –column firstname format a20
    –column tag format a4 heading ‘tag’

    select a.col, b.col, c.col, d.col
    , count(*)
    from
    table@link A
    , table B
    , table c C
    , table d d
    where
    a.bid = b.id
    and
    a.cid = c.id
    and
    a.did = d.id
    and
    a.another_id in
    (
    10, 30, 50
    )
    and
    rownum <100
    and
    a.tag = 'ES'
    and
    a.created_date between '01-JAN_18' and '30-APR_18'
    order by a.id, a.email_activity_tag
    group by a.col, b.col, c.col, d.col
    ;

    You know your stuff, so I'm hoping you can spot something I'm doing improperly. I've checked commas, brackets, spelling, etc. to no avail. This is being run in Encompass Designer which is our company program for setting up campaigns, but it's running Oracle, and I just don't see the problem. If you can help, thanks.

    1. Select salary from emp order by salary desc limit(1,n-1);
      ORA-00933: SQL command not properly ended

      Hi ben,

      While trying to execute the above query. I am facing this error SQL command not properly ended.

      Please help me to resolve. I have checked our databasestar page also. But still it is getting confused.

      1. Hi Antony, this may be because of the Limit keyword. Oracle does not use the Limit keyword, so you’re getting this error. You can try some of the techniques in this guide, such as the Fetch keyword: SQL Limit.

  2. I forgot to include the actual error message:

    group by a.col
    *
    ERROR at line 41:
    ORA-00933: SQL command not properly ended

    1. Hi Paul,
      Thanks for the question!
      I think the issue is that your GROUP BY comes after the ORDER BY. SQL requires that the ORDER BY is the last line in the query, even after GROUP BY and HAVING and everything else.
      I’ve rearranged your query to put the ORDER BY last, which should work:

      select a.col, b.col, c.col, d.col
      , count(*)
      from
      table@link A
      , table B
      , table C
      , table d d
      where
      a.bid = b.id
      and
      a.cid = c.id
      and
      a.did = d.id
      and
      a.another_id in
      (
      10, 30, 40
      )
      and
      rownum <100
      and
      a.tag = 'ES'
      and
      a.created_date between '01-JAN_18' and '30-APR_18'

      group by a.col, b.col, c.col, d.col
      order by a.id, a.tag;

      Also, have you considered using JOIN keywords instead of matching on tables in the WHERE clause? JOINs are generally preferably because they explicitly say how two tables are joined, and it's easy to tell them apart from WHERE conditions that filter data.
      This query has been rewritten to use JOINs and should show the same result:

      select a.col, b.col, c.col, d.col, count(*)
      from
      table@link A
      INNER JOIN table b ON a.bid = b.id
      INNER JOIN table c a.cid = c.id
      INNER JOIN table d a.did = d.id
      where
      a.another_id in
      (
      10, 20, 30
      )
      and
      rownum <100
      and
      a.tag = 'ES'
      and
      a.created_date between '01-JAN_18' and '30-APR_18'
      group by a.col, b.col, c.col, d.col
      order by a.id, a.tag;

      Let me know if you have any issues/questions with this!
      Thanks,
      Ben

  3. Paul Bartells

    Thanks, Ben. I eventually figured it out. It’s strange how sometimes the basic constructs of a language can trip one up. So, I got it fixed, but I appreciate that you went the extra step of recommending using JOINs instead of WHERE conditions. I’ll admit I don’t really know the rules that would determine that choice. Obviously, both approaches work for this query, but are there case where the WHERE conditions would be preferable to JOINs?

    1. No problem!
      I don’t know if there would be a situation where the WHERE conditions are preferable to JOINs. Perhaps in a small query, or something that’s just quickly written to test some data, it might be easier to write it using WHERE conditions.
      Actually, one reason to do it this way is if you’re following coding standards in your team. If, for some reason, the team has determined they want to write JOINs that way, then it’s often better to stick with the team standard.
      – Ben

  4. SELECT distinct sfbetrm_pidm AS pidm,
    spriden_id AS Student_ID,
    spriden_first_name
    || ‘ ‘
    || spriden_last_name AS Student_Name,
    sfbetrm_term_code AS term,
    sfbetrm_ests_date AS ests_date,
    sfbetrm_ests_code AS ests_code,
    sfbetrm_activity_date AS latest_update
    FROM (SELECT distinct sfbetrm_pidm,
    sfbetrm_term_code,
    Rank()
    OVER (
    partition BY sfbetrm_pidm
    ORDER BY sfbetrm_ests_date DESC) AS rnk,
    spriden_id AS Student_ID,
    spriden_first_name
    || ‘ ‘
    || spriden_last_name,
    sfbetrm_ests_date,
    sfbetrm_ests_code,
    sfbetrm_activity_date
    FROM sfbetrm
    LEFT JOIN spriden
    ON spriden_pidm = sfbetrm_pidm) sfbetrm
    LEFT JOIN spriden
    ON spriden_pidm = sfbetrm_pidm
    WHERE sfbetrm.rnk = 1
    AND sfbetrm_term_code >= 201908
    AND sfbetrm_term_code <= 202008
    and spriden_change_ind is null;

  5. I am seeing this error when using a very basic query that seems to comply with all the rules (and runs OK when run on Oracle).

    I have stripped back my query to make it as simple as possible to get this working. The query I am trying to run against the linked server is a straight-forward SELECT with no JOINs or WHERE clauses. There are 37 rows in the target table.

    ————–

    DECLARE @sqlQuery AS VARCHAR(MAX);
    SET @sqlQuery = ‘SELECT BRAND_NAME FROM BRAND;’;

    EXECUTE (@sqlQuery) AT xxUAT;

    ————–

    OLE DB provider “OraOLEDB.Oracle” for linked server “xxUAT” returned message “ORA-00933: SQL command not properly ended”.

    OLE DB provider “OraOLEDB.Oracle” for linked server “xxUAT” returned message “ORA-00933: SQL command not properly ended”.

    Could not execute statement on remote server ‘xxUAT’.

    ————–

    I am able to query against the linked server using OPENQUERY, but I am looking for a solution that gets around the 8000 character limit of OPENQUERY for when I need to execute larger queries.

    I have run this previously, to support querying linked server this way:
    EXEC master.dbo.sp_serveroption @server=N’xxUAT’, @optname=N’rpc out’, @optvalue=N’true’;

    1. Hi Keith, that sounds like a tricky error. I haven’t used linked servers in a while so I’m not sure why this query would not be working. Perhaps it doesn’t like the ; inside the query, but that’s just a guess. If that doesn’t work, you could try asking a question on Stack Overflow – they’re usually pretty good with these tricky and specific questions!

      1. That’s it Ben… the semicolon inside the statement being passed to the Linked Server! Take that out and it works. Many thanks.

  6. select sv.masv
    from dhcntt.sinhvien sv
    except
    select sv.masv
    from dhcntt.sinhvien sv, dhcntt.hocphi hp
    where sv.masv = hp.masv

    THE ERROR WAS SQL COMMAND COULD NOT END PROPERLY. PLEASE SOLVE THIS FOR ME.

Leave a Comment

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

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