Hi there,
We are currently carrying out an analysis of updating our application to use Oracle 12.2 and subsequently the version of the ojdbc driver shipped with that version of the database. This is ojdbc8.jar which is Java 1.8 compatible only. The previous version of the driver which was used to test our product with was the ojdbc6.jar shipped with Oracle 12.1.
As a result of this analysis, we have noticed different behavior in the area of executing dynamic SQL statements. The following example highlights the difference:
1. Developers may write hand crafted SQL statements which are executed dynamically and which should be reads on the database (i.e. Select .... from .....).
2. We have a validation routine which ensures that these queries do not contain SQL which does not represent a query (i.e. an insert, update or remove).
3. We have tests which ensure that this validation routine works and throws the required exception when the SQL does not represent the correct syntax. These tests consist of trying to execute the query with the following statements:
- INSERT ...
- UPDATE ....
- DELETE ...
4. All three of these tests threw the expected exception when executed against the previous version of the 12.1 driver (ojdbc6.jar).
5. When executing the same tests against the new version of the 12.2 driver (ojdbc8.jar), the INSERT SQL statement is throwing the required exception but the other two statements (DELETE and UPDATE) are not.
From a technical perspective, the mechanism used to ensure that the statements were correct was to:
1. Execute the query using the supplied SQL.
2. Attempt to fetch a row from the resultant resultset.
3. This threw an exception (ORA-01002: fetch out of sequence) using the old driver which our tests exploited as being invalid SQL being used for a SQL Select query.
4. For DELETE and UPDATE statements, this no longer the case using the new driver (which instead now executes without exception and returns 0 rows). Executing an INSERT statement seems to be consistent with the old driver in it's behavior.
We are wondering:
1. Has something changed in this area in the ojdbc8.jar driver implementation which is different to that of ojdbc6.jar which would cause this different behavior (in the area of executing queries and those executions returning a different resultset or changes to that resultant resultset or the "fetch()" function on a resultset exhibiting different behavior)?
2. Rather than attempting to execute the SQL statement, attempt to fetch a row and catch the resultant exception (i.e. by calling executeQuery() and passing in invalid SQL such as an UPDATE or DELETE), is there another mechanism which can be exploited to ensure that when executing SQL queries, the correct SQL is used (i.e. SELECT rather than INSERT, UPDATE or DELETE). Note that this is a validation routine only and does not commit any data to the database as a result.
Thanks