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-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.
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:
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
[email protected] 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.
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.
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.
pls let me know how it is resolved
I forgot to include the actual error message:
group by a.col
*
ERROR at line 41:
ORA-00933: SQL command not properly ended
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
[email protected] 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
[email protected] 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
getting error SP execution error :-
[Error] Execution (2: 1): ORA-00933: SQL command not properly ended
EXECUTE IMMEDIATE v_sql1; is executing insert statment :-
INSERT INTO MREFICT.AVNT_APPLICATIONS SELECT * from mreadm.AVNT_APPLICATIONS WHERE ACCOUNTING_PERIOD >= ‘112021’ AND ACCOUNTING_PERIOD = ‘112021’ AND ACCOUNTING_PERIOD = ‘112021’ AND ACCOUNTING_PERIOD = ‘112021’ AND ACCOUNTING_PERIOD = ‘112021’ AND ACCOUNTING_PERIOD <= '112021' ;
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?
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
sql command not properly ended
* cause
* action
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;
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’;
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!
That’s it Ben… the semicolon inside the statement being passed to the Linked Server! Take that out and it works. Many thanks.
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.
This is likely due to the EXCEPT keyword which works in SQL Server but not in Oracle. Could you try the same query but with MINUS?
Hi Ben
I have a similar error, I did change my query leaving ORDER BY statement at the end, but I still have the same error message: ORA-00933: SQL command not properly ended.
This is my query:
WITH C AS (SELECT COD_CONC, DES_CONC, SOCIETA
FROM SVVEN_STAGE.CONCESSIONARIO
WHERE COD_TIPO_CONC = 1),
LV AS (SELECT COD_CONC, COUNT(*) NUMERO
FROM SVVEN_STAGE.LOG_VENDITA
WHERE DATA_ORA_VEND_UTC > SYS_EXTRACT_UTC(SYSTIMESTAMP) – INTERVAL ‘1’ MINUTE
AND COD_CONC 15226
GROUP BY COD_CONC)
SELECT C.SOCIETA, SUM(NVL(LV.NUMERO, 0)) NUMERO
FROM C
LEFT OUTER JOIN LV ON (LV.COD_CONC = C.COD_CONC)
GROUP BY C.SOCIETA
ORDER BY C.SOCIETA;
works fine in sqldeveloper, but it does not work on visual studio code (python script)
Thanks Ben… solved :)
What was the resolution?
select
regexp_substr(req_id,'[^,]+’, 1, level)
FROM (
select
–regexp_substr(patv.task_comments,'[^,]+’, 1, level) as request_id,
patv.task_comments req_id,
‘Cancel the IT request’ Comments,
papf.person_number,
papf.person_id,
pacv.allocated_checklist_id abs_allocated_checklist_id,
patv.allocated_checklist_id,
patv.allocated_task_id
from
per_all_people_f papf,
per_allocated_tasks_vl patv,
per_allocated_checklists_vl pacv
,PER_PERSON_TYPE_USAGES_M PPTUM
,PER_PERSON_TYPES_VL PER_TYPES ,
PER_ACTION_OCCURRENCES PAOCC, PER_ACTIONS_B PACTB
where
papf.person_id=pacv.person_id
AND PAPF.PERSON_ID = PPTUM.PERSON_ID(+)
AND PPTUM.PERSON_TYPE_ID = PER_TYPES.PERSON_TYPE_ID
AND PER_TYPES.SYSTEM_PERSON_TYPE = ‘CANCELED_HIRE’
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND trunc(SYSDATE) BETWEEN PPTUM.EFFECTIVE_START_DATE AND PPTUM.EFFECTIVE_END_DATE
AND upper(patv.task_name)=upper(‘It Request task Completed?’)
AND patv.STATUS=’COM’
AND PAOCC.ACTION_ID = PACTB.ACTION_ID
AND PACTB.ACTION_CODE = ‘EMPL_CANCEL_WR’
AND PAOCC.PARENT_ENTITY_KEY_ID=papf.person_id
AND patv.ABS_ALLOCATED_CHECKLIST_ID=pacv.allocated_checklist_id
AND patv.object_version_number
= (select max(object_version_number) from per_allocated_tasks_vl where allocated_task_id=patv.allocated_task_id)
AND pacv.checklist_name in (‘Saudi National Mobilization’,’Non-Saudi Outside Kingdom Mobilization’,’Non-Saudi National Local Transfer Mobilization’)
AND pacv.object_version_number = (select max(object_version_number) from per_allocated_checklists_vl
where pacv.checklist_id=checklist_id and pacv.person_id=person_id )
AND PAOCC.last_update_date >= to_date(:p_last_run_date,’DD-MM-YYYY HH24:MI:SS’))
connect by regexp_substr(req_id, ‘[^,]+’, 1, level) is not null
Giving ORA-00933 error
What was the resolution?
when i am creating a sequence in the database they showing error,
Error starting at line 5 in command:
CREATE SEQUENCE “RI”.”ADHOC_COMMITTE_NOTICES_ID_SEQ” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL
Error at Command Line:5 Column:160
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 – “SQL command not properly ended”
*Cause:
*Action:
Olá, estou recebendo essa exception ao tentar reabilitar uma foreign key que desabilitei para inserir campos.
usei
ALTER TABLE BRH2.COLABORADOR DISABLE CONSTRAINT BRH2.FK_DEPARTAMENTO_COLABORADOR;
funcionou normalmente, mas na hora de reabilitar usando
ALTER TABLE BRH2.COLABORADOR ENABLE CONSTRAINT BRH2.FK_DEPARTAMENTO_COLABORADOR;
recebo esta mensagem de erro.
het im getting a error ” ORA-00933: SQL command not properly ended ”
in this query
INSERT INTO PS_X_N_LVL_1(INSTITUTION,ACAD_CAREER,ACAD_PROG,EMPLID)
SELECT INSTITUTION,ACAD_CAREER,ACAD_PROG,EMPLID FROM PS_ACAD_PROG
INSERT INTO PS_X_N_LVL_1 (STRM)
SELECT STRM FROM PS_STDNT_CAR_TERM
LEFT JOIN PS_X_N_LVL_1 ON PS_X_N_LVL_1.EMPLID = PS_STDNT_CAR_TERM.EMPLID
WHERE PS_X_N_LVL_1.EMPLID=’00000343′;
Hi Ben,
I have query written, it works fine in SQL developer but fails in fetching data from JDBC URL.
Kindly help me with this.