Knex OracleDB - Error thrown if table name case mismatch occurs.

[tejzpr]

I was able to connect to Oracle 12 by installing nodejs/oracledb (OracleDB driver support is there in knex even though it is not mentioned in the docs.#990) driver

oracle: {
    client: 'oracledb',
    connection: {
      timeout: 10,
      host: 'localhost:1521',
      //port:'1521',                **//Declaring port here does not work**
      user: 'username',
      password: 'password',
      database: 'mydb'
    },
    debug: true
  }

But even after the connection got through, any queries made to OracleDB resulted in an error. I am using a pre-defined table called 'USERS' (notice uppercase). When the query is made to OracleDB an error occurs like this: Error: select * from "users" - ORA-00942: table or view does not exist
Seems passing table names in quotes does not work with Oracle. because table names in Oracle is case sensitive.

[elhigu]

@tejzpr hi, you are right that support for oracledb driver doesn't seem to be documented. We should update also that to documentation. It is actually the only oracle driver which is tested automatically.

About case sensitivity, you need to write your queries case-sensitive way. e.g.

knex('USERS').toSQL()

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '0ef00717-b86a-4c24-82d3-3264916eecf9',
  sql: 'select * from "USERS"' }

IIRC the integration tests are using Oracle 11 so version 12 is not really tested, but I suppose it should work the same.

There has been more discussion about case sensitivity of oracle here: #1761

[tejzpr]

@elhigu Hi, thank you for clarifying. Another issue that I noted with oracledb driver setup was that the Port configuration as a separate field in DB configuration doesn't work. The port needs to be passed along with the host string.

[elhigu]

AFAIK knex dialects passes configuration pretty directly to the driver, does oracledb driver support port attribute? I didn't see it here https://github.com/oracle/node-oracledb/blob/master/doc/api.md