ORA-00933: SQL command not properly ended. CF9 Error Executing Database Query, using ojdbc6 driver with Insert statement using select query.

[Guest]

Hi,

When using the CF9 in-build Oracle driver, the query works well, but when it is changed to use the ojdbc6.jar, then getting the below issue.

ORA-00933: SQL command not properly ended.

Issue particularly happens when we do an Insert using a select query inside cfquery. i.e.

INSERT INTO Table1
  (
  id
  , name
  , viewtime
  )
  SELECT
  tab_id as id
  , name
  , viewtime
  From Table2

[haxtbh]

What is your reason for changing the driver?

[Guest]

Because we are migrating from oracle 11g to 12c and the CF embedded driver does not work with oracle 12c.

For the next Question, yes we are planning to migrate the app to CF 11, but oracle is queued first.

[haxtbh]

CF9 does not support 12c. You will have to upgrade to CF11 before changing to 12C

[Guest]

haxtbh, Shall we run into some other issue rather than the driver if we use Oracle 12c with CF9, please educate if you see any other issue.

Further info, we are getting the above issue only on one server and the error details below,

ORA-00933: SQL command not properly ended.

VENDORERRORCODE       933

SQLSTATE       42000

SQL

[haxtbh]

As well as compatibility issues with CF9, the error you are getting is related directly to Oracle. You could be using outdated SQL that has now been fully depreciated in 12c. You will be better asking somewhere that helps with Oracle related queries.