The following simple named query is failed to execute in camel-sql component when we upgraded our OJDBC drivers from ojdbc6 to ojdbc7 (12.1.0.2). Further debugging found that camel-sql component is depend on ParameterMetaData getParameterMetaData() to get the named parameter count. In ojdbc7 this method results in error due to the sql parsing behaviour.
Provided the simple SQL query which failed during the execution.
SELECT some_id FROM app_schema.app_store_table WHERE store_id = :#storeId and source = :#source;
Exception:
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.OracleConnection isValid
FINE: 19C71BE Exit [17.696372ms]
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Public Enter: "**SELECT some_id FROM app_schema.app_store_table WHERE store_id = ? and source = ?**"
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Return: oracle.jdbc.driver.OraclePreparedStatementWrapper@c2c269
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Exit [226.482362ms]
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Public Enter: "***SELECT store_id, source FROM app_schemaapp_store_table***" <-- .[DOT] between schema_name and table_name is replaced by the driver which results in this error.
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Return: oracle.jdbc.driver.OraclePreparedStatementWrapper@e1e592
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 19C71BE Exit [0.413825ms]
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.OraclePreparedStatement getMetaData
FINE: E1E592 Public Enter:
Aug 30, 2017 9:06:57 AM oracle.jdbc.driver.T4CTTIoer processError
SEVERE: CD5A42 Throwing SQLException: ORA-00942: table or view does not exist
This can be replicated easily using the following command
-- example for .[DOT] getting replaced between schema_name and table_name. Refer Parameter SQL.
C:\Users\vm\Desktop\ojdbc\oracle>java -cp ojdbc7_g-12.1.0.2.jar oracle.jdbc.driver.OracleParameterMetaDataParser "*SELECT some_id FROM app_schema.app_store_table WHERE store_id = ? and source = ?*"
SQL:SELECT some_id FROM app_schema.app_store_table WHERE store_id = :1 and source = :2
SqlKind:SELECT, Parameter Count=2
Parameter SQL: SELECT store_id, source FROM app_schemaapp_store_table <- .[DOT] getting replaced between schema_name and table_name this is how it is being parsed by the OracleParameterMetaDataParser class
-- example for invalid parsing of the query. Refer Parameter SQL.
C:\Users\vm\ojdbc\oracle>java -cp oracle.jdbc.driver.OracleParameterMetaDataParser "*SELECT f_some_id FROM app_store_table WHERE f_store_id = ? and f_source = ?*"
SQL:SELECT f_some_id FROM app_store_table WHERE f_store_id = :1 and f_source = :2
SqlKind:SELECT, Parameter Count=2
Parameter SQL: SELECT f, f FROM app_store_table <- this is how it is being parsed by the OracleParameterMetaDataParser class
For more details, follow this link https://stackoverflow.com/questions/45927863/ojdbc7-camel-sql-component-failed-to-execute-queries-after-upgrading-to-ojd…