Oracle – ORA-00922: missing or invalid option
Hello There!
I recently did test in my Java code to have a SQL statement for copy table from TABLE 1 toTABLE 2. I need both tables identical during the application goes boot-up. But, suddenly i got :
ORA-00922: missing or invalid option.
1. ORACLE DOCUMENTATION
Based on Oracle error documentation ORA-00922 says :
| Cause | An invalid option was specified in defining a column or storage clause. The valid option in specifying a column was NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error. |
| Action | Correct the syntax. Remove the erroneous option or length specification from the column or storage specification. |
At the action clue, there is a phrase that says, ‘Correct the syntax.’ This seems quite odd as the query statement appears to be fine.
I went down to run the query via Oracle Developer to fulfil my curiosity to test my query directly. You know what, It was running ok though 🧐.
As you can see, the table DUMMY_BACKUP is created. I checked my code, it was :
String sql = String.format("CREATE TABLE %s FOR EXCHANGE WITH TABLE %s;", detinationTable, sourceTable);
stmt.executeUpdate(sql);2. WHAT IS THE SOLUTION THEN?
After i tweak my code and noticed there is a semicolon of the statement and did removed semicolon(;) at the end of SQL string.
Oracle SQL doesn’t have statement terminators. Client tools and PL/SQL solely use semicolons to separate SQL statements, such as when executing scripts with SQL*Plus’s start command, but they are not part of the SQL statement itself.
In fact, Oracle will throw an ORA-00922 error when it tries to parse such an SQL.
String sql = String.format("CREATE TABLE %s FOR EXCHANGE WITH TABLE %s", detinationTable, sourceTable);
stmt.executeUpdate(sql);I re-compile the java code and try to run it again, the result of ORA-00922 has been disappeared. The code worked as expected.
It’s actually simple error, but you will got confuse if there is no clue about it 😎.
Note : Java 17 and Oracle 19c
However, if you’d like to view my other note related to Oracle and REST API, you can visit this.
That’s all folks! Happy coding! 🥰😍
