The Oracle return error call “ORA-00933: SQL command not properly ended” once execute a DML operation. That indicates that there’s a problem with your SQL statement syntax.
This error triggers when trying to execute SQL commands that are not properly written or not properly used. Below showing the correct and incorrect ways of writing sql commands.
Error Message Syntax:
-- ORA-00933: SQL command not properly ended --
1. Missing Semicolon:
The most usual way of error SQL command not properly ended triggering is missing a semicolon (;). This can happen when you run multiple commands and when you miss a colon in any of the single commands.
Example
Correct
-- SELECT column1 FROM table_name; SELECT column2 FROM table_name; -- Make sure each statement ends with a semicolon--make sure each statement ends with a semicolon --
Wrong
-- SELECT column1 FROM table_name SELECT column2 FROM table_name; --
In this simple code missed semicolon in the first statement.
2. Incorrect Syntax:
Verify the use of correct syntax to command you execute
Example:
Correct
-- SELECT column1 FROM table_name WHERE column2 = 'value'; --
Wrong
SELECT column1 FROM table_name WHERE column2 = 'value'
missing the semicolon at the end
3. Improperly Placed Clauses:
Place the keyword in the proper order. WHERE, GROUP BY, ORDER BY, and other keywords should write in the correct order.
Example:
Correct
-- SELECT column1 FROM table_name WHERE column2 = 'value' ORDER BY column1; --
Wrong
-- SELECT column1 FROM table_name ORDER BY column1 WHERE column2 = 'value'; --
Where Clouse should come earlier with Order by Clouse.
4. Missing or Extra Parentheses:
Check that all the brackets are closed in the correct position this mistake can raise the error ORA-00933: SQL command not properly ended. But in two ways. If you miss one or more brackets or close in the wrong position.
Example:
Correct
-- SELECT column1 FROM table_name WHERE (column2 = 'value'); --
Wrong
-- SELECT column1 FROM table_name WHERE (column2 = 'value'; --
Example showing brackets not closed at the end.
5. Incorrect Use of Keywords:
We can’t have mix keywords in an incorrect way.
Example:
Correct
-- SELECT column1 FROM table_name WHERE column2 = 'value' AND column3 = 'value'; --
Wrong
-- SELECT column1 FROM table_name WHERE column2 = 'value' AND WHERE column3 = 'value'; --
Where keywords cannot be used multiple times
6. Unterminated Strings:
Strings are heavily used as a parameter but use single quotes for open and closed strings.
Example:
Correct
- SELECT column1 FROM table_name WHERE column2 = 'value'; --
Wrong
-- SELECT column1 FROM table_name WHERE column2 = 'value; --
unterminated string. You have to end once you open the string quotes.
7. Unmatched Brackets or Quotation Marks:
Brackets and quotations should be matched. An opening quotation should be matched for opening and closing quotations and brackets
Wrong
-- SELECT * FROM table_name WHERE column_name = "value'; --
8. Syntax error on CREATE VIEW or INSERT
Consider Once you want to write CREATE VIEW or INSERT statement. But when you write a statement with CREATE VIEW or INSERT we are not allowed to use order by course. It’s because we do not use order once you are going to insert data into the table.
Wrong
-- UPDATE first_table SET first_table.first_column = ‘New Value’ INNER JOIN second_table ON first_table.id = Second_table.column_id; --
Order by can be used to retrieve data from a table. Also, we can use order by clause in the view but not for the create view or insert statements.
Don’t forget that ORDER BY cannot be used to create an ordered view or to insert in a certain order.
9. UPDATE statement with joining tables
In Oracle joining table is not allowed for updated statements. Oracle does not allow joining tables in an UPDATE statement. As a solution, you can write a subquery for selecting from reference tables.
In that subquery you are allowed to use joins because it’s a select query as we write to fetch data from a table.
Something like this: Correct
--
UPDATE mytable
SET my_columne = 'Value'
WHERE my_column_id IN (SELECT column_id
FROM second_table
WHERE my_second_column = 'other value')
--10. A DELETE statement with an INNER JOIN or ORDER BY clause
Those are the incorrect use of syntax that raise an error “ORA-00933: SQL command not properly ended”: These simple examples are related to misuse of syntax. In Oracle “ORA-00933: SQL command not properly ended” Always indicates your SQL statements follow the correct syntax and that might be a problem with your quotation or semicolons.
How to Fix the Error:
- You can check if there are any errors in syntax like misplaced semicolons, keywords, and symbols.
- Recheck whether you have opened any quotation or parentheses are properly closed
- Make sure the Order of the Sysntax are correct in other word follow the standard when using Oracle syntax
- .Finally, you can follow the Oracle documentation for the correct way of using syntax
