oe-connector-oracle

This is a oracle connector for oeCloud framework developed based on loopback-connector-postgresql with the same license of postgres connector.

Getting Started

In your application root directory, enter this command to install the connector:

$ npm install oe-connector-oracle oracledb --save

This will install the module and add’s it to application’s package.json file.

Usage Guidelines

  • All models by default should be strict:true. Any property you want to POST/GET must be defined in your model definition. The reason for this is, each property defined on the model is stored as a column in the table and if you try to GET/POST any new property(not defined on model), Oracle will throw an error because there is no column defined for such properties.
  • Queries on Object types should be avoided. For example on one record prop1.sub is numeric and in other record prop.sub is string then oracle will throw error as it cannot convert it to specific type.
  • We should consider strongly typed properties in the model. For example instead of using property1:{type: any} Its adviceable to use property1:{type: object}. Another important thing we need to be careful is with array types. It would be easy to query on ["string"] than [any]/ [].
  • While changing the model definition for the existing Model, we should be careful while adding new properties on the model with validations. For example we have a model with 10 records in db with 5 properties. Now we want to add a new property with required validation. By directly adding this to model will leave the current table in in-consistent state as there are already existing records without any value for that column. We can do this safely with causing these issues by adding a defaul value in the newly defined property on tha model.
  • In model definition, properties can include mapping for standard Oracle types as shown below.
"properties":{
      "productId":{
        "type":"string",
        "required":true,
        "length":20,
        "id":1,
        "oracle":{
          "columnName":"PRODUCT_ID",
          "dataType":"VARCHAR2",
          "dataLength":20,
          "nullable":"N"
        }
      },
      "locationId":{
        "type":"string",
        "required":true,
        "length":20,
        "id":2
      },
      "available":{
        "type":"number",
        "required":false,
        "length":22,
        "oracle":{
          "columnName":"AVAILABLE",
          "dataType":"NUMBER",
          "dataLength":22,
          "nullable":"Y"
        }
      },
      "total":{
        "type":"number",
        "length":15
      }
    }
  • Any filter query on the properties which are not actually part of model definition properties will be ignored by default. This will give you unexpected results based on your query which will not be same as mongodb. For example, if your model has property foo and your filter query is bar:1. Then mongo will return [] (Empty array). where as Oracle will return all the records because the bar:1 filter will be ignored because its not a defined property.

  • For troubleshooting, it may be required to see the queries generated by the connector. Two things are required. (a) add debug to true in the datasource connector properties in the main application, and, (b) set the DEBUG environment variable (e.g. export DEBUG=loopback:connector:oracle). A general recommendation here would be set the debug property based on reading the environment variable via nodejs (process.env.DEBUG). This way we only need to set the environment variable prior to startup.

Sequence support

As of version 2.1.0 of the connector, support for consuming sequence objects is provisioned. Unlike oe-connector-postgresql module, this connector has only two kinds of sequences - simple and complex.

Only one property in a model can be defined to support an oracle sequence object. The property should be such that it uniquely identifies the instance. Hence it should be an id field.

Simple Sequence

Supports a simple sequence - it is similar to having the sequence object created in the database and a corresponding table consuming it through a column whose default value is appropriately set. For e.g. below code (i.e. model definition) creates a sequence with name reservation_sequence and a table named testschema2

{
  "name": "testschema2",
  "properties": {
    "reservationId" : {
      "id" : true,
      "oracle": {
        "sequence" : {
          "type": "simple",
          "name": "reservation_sequence"
        }
      }
    },
    "firstName": "string",
    "lastName": "string"
  }
}

Note: Only one property in the model can consume a sequence, and, it also must uniquely identify an instance. (Therefore "id" : true is part of the corresponding property definition)

Below strech of code describes the configuration required for defining a simple sequence.

const SEQ_SIMPLE = {
  name: null,       // sequence name - required
  incrementBy: 1,
  minValue: false,  // number or boolean
  maxValue: false,  // number or boolean
  startFrom: 1,     // number
  cache: false,     // Specify (integer) how many values of the sequence the database preallocates and keeps in memory for faster access. 
                    //    Must be >=2. The integer should have less than or equal to 28 digits.
                    //    Alternatively specify boolean. 
                    //      A boolean true means cache value adopts 2. 
                    //      A boolean false means no caching (default)
  cycle: false,     // restart once the seq reaches its upper bound.
  order: false      // guarantee sequence numbers are generated in order of request. Default false
};

Note: Please refer oracle documentation for more details about each parameter.

Complex sequence

This connector also supports prefix based sequences. A prefix is a string which is prefixed to a padded sequence number. This makes it possible to generate sequences such as LMB00001, LMB00002, LMB00003, etc.

It has all the configuration of a simple sequence, and, the following parameters:

const SEQ_COMPLEX = Object.assign({}, SEQ_SIMPLE, {
  name: null,       // sequence name - required
  length: 0,        // final length of prefix-ed sequence - required
  prefix: null      // the prefix to appear before the padded sequence - required
});

Example:

{
  "name": "testschema3",
  "properties": {
    "reservationId" : {
      "id" : true,
      "oracle": {
        "sequence" : {
          "type": "complex",
          "name": "reservation_sequence",
          "prefix": "LMB",
          "length": 10
        }
      }
    },
    "firstName": "string",
    "lastName": "string"
  }
}

Index usage with like operator

Like operator can be used in 4 ways in our queries:

  1. Search-String%
  2. %Search-String
  3. %Search-String%
  4. Search%String

Index range scan is only done in for cases like Search-String% and Search%String. While using %Search-String and %Search-String% full table scan is done.

SELECT FOR UPDATE support

In a read-commited transaction, oracle supports locking rows in table for the duration of the transaction by means of appending a FOR UPDATE clause at the end of a SELECT query.

To enable this, we need to pass the filter to a Model.find() as follows:


Customer.find({ id: 'xijqpw', selectForUpdate: true }, {}, function(err, results){

});

Limitations and considerations

  1. Any limit/skip, (i.e. FETCH NEXT and/or OFFSET) clauses will not work.
  2. Its the developer’s responsibility to do this within a transaction.

Pagination support

Oracle 12c (and greater) has support for FETCH NEXT and OFFSET clauses. They can be enabled by adding the following property in the application’s datasource configuration (datasources.json) (in the corresponding connector):

{
  "supportsFetchOperation": true
}

Performance considerations

The following properties in the connector are important

poolMin and poolMax

Where possible their values must be equal to avoid connection storming.

The poolMax value must be less than or equal to nodejs’s UV_THREADPOOL_SIZE

Connecting to a SSL enabled oracle db server

A few artifacts need to be gathered during the oracle db server SSL configuration. Some of these artifacts need to be modified before use in oe-cloud application. The steps are outlined below.

This setup assumes that oracle instant client libraries are installed.

1. Prequisites (To be gathered from the oracle DBA)

  1. Oracle wallet (with the SSL certificate). (as zip)
  2. sqlnet.ora - Corresponsing entries used to create the wallet. All entries expect for WALLET_LOCATION or ENCRYPTION_WALLET_LOCATION needs to be exactly the same.
  3. tnsnames.ora - make note of the tnsname and the corresponding configuration
  4. The oracle server hostname or ip address, and, port which is configured for SSL.
  5. Distinguished name, used for creating the server certificate.

2. Setting up oe-cloud application connector

  1. Unzip the oracle wallet folder to the filesystem. (Hereafter contents of this folder are collectively called A).
  2. Copy the sqlnet.ora and tnsnames.ora file to the same folder on the filesystem. (Hereafter these items are collectively called B)
  3. Modify the sqlnet.ora and update WALLET_LOCATION parameter. This should point to the folder containing A.
  4. Set environemnt variable TNS_ADMIN. The value should be the path to folder containing B.
  5. In the server/datasources.json the entry corresponding to the datasource using oe-connector-oracle, must only use connectString. It should have value that is the tnsname for target oracle db server instance. This obviates the need for host, port, and database parameters.

3. Concluding notes

  1. Both A and B can be in the same folder.
  2. For #3 above the parameter to update can alternatively be ENCRYPTION_WALLET_LOCATION
  3. For #5, the connect descriptor can be provided as alternative to tnsname. In this case, the tnsnames.ora file is ignored.
  4. If an error indicates that a host is unreachable, you may need to replace the host name with IP address. Alternatively, have a entry in /etc/hosts file which maps the host name with that ip address.

Connecting to oracle DB server using external password store(Oracle wallets)

  1. Setup the oracle db server for SSL (To be done by DBA).

    • These artifacts are created in this activity, and, required later.

      • sqlnet.ora
      • tnsnames.ora
      • wallet folder (containing cwallet.sso and ewallet.p12)
      • tns name & service name (can be found in tnsnames.ora, but make note of it.)
    • Note: sqlnet.ora and tnsnames.ora need to be modified. It is mentioned in the below steps.

  2. Setup the oe-cloud application (oracle-connector and environment)

    2.1 Make the wallet folder available at a suitable path in the file system. (E.g. in the project root itself ./wallet)

    2.2 Copy the sqlnet.ora and tnsnames.ora to the filesystem.

    Note: It can be any path. Assuming here they are copied into the oracle wallet folder.

    2.2.1 Modify the WALLET_LOCATION parameter in sqlnet.ora file. Update the full path to the oracle wallet folder.

    Note: All other parameters, (e.g. cipher suits etc) should match the server sqlnet.ora file.

    Example:

    NAME.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
    
    WALLET_LOCATION =
      (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
          (DIRECTORY = D:\work\sample\wallet01)
        )
      )
    
    SQLNET.WALLET_OVERRIDE = TRUE
    

    2.2.2 In the tnsnames.ora file have a tns name entry. Important properties: PROTOCOL, HOST, PORT, SERVER, SERVICE_NAME.

    In the example below, ORCLCDB is the tns name

    ORCLCDB= 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.73.74.176)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = ORCLCDB)
      )
    )		
    

    2.3 Set the TNS_ADMIN environment variable to point to where sqlnet.ora and tnsnames.ora are located.

    2.4 In the datasource.json file, the properties you DO NOT REQUIRE are the “host”, “database” (or service name), and “port”.

    Note: You need to set externalAuth as true and specify connectString with the tns name.

    Example:

    "db": {
        "name": "db",
        "connector": "oe-connector-oracle",
        "connectString": "ORCLCDB",
        "externalAuth": true
      }
    

Programmatic id generation

The connector can allow developers to provide their own id generation logic during new record creation. To enable this we need to do the following:

  1. Use the customized datasources config file (e.g. server/datasouces.local.js)
  2. set enableConnectorIdGeneration to true
  3. set idGenerateFn to the following a function which takes model name as first argument, and, the data (or payload) as the second argument. The function should return a id value or undefined. If the latter, the value of the property will default to whatever is defined in the database/table/field. The context of this function will be the connector instance.

Database Indexes

The connector can generate indexes while creating tables in the database. Their specification can be defined in the model definition json files. The two styles supported are:

  1. Field level declaration
  2. Model level declaration

Field level index declaration

In the model-definition json it is defined as part of the property definition. For e.g.,

{
    // ...
    "properties": {
        "fieldName": {
            "type": "string",
            "index": true
        },
        // ...
    }
    // ...
}

This will generate an index on the column fieldName. If a unique index is required then it can be written as follows:

{
    "name":"Customers",    
    "properties": {
        "fieldName": {
            "type": "string",
            "index": { "unique" : true}
        },
        // ...
    }
    // ...
}

The name of the index object in the database in auto-generated. It will be a combination of the model name, and, field name. For each field defined as an index, a separate index object will be created.

Model-level index declaration

This style of index creation allows developers to define multi-column indexes. Below is the general pattern of specifying an index in the model definition json:

{
    "name":"Customers",    
    "properties": {
        "field1": "string",
        "field2": "string",
        "field3": "number",
        // ...
    },
    // ...
    "indexes": {
        "index_name" : {
            "keys": {
                "field1": 1,
                "field2": 1,
                "field3" : 1,
                // ...
            },
            "options": {
                "unique": true
            }
        }
    },
    // ...
}

This will create a multi-column unique index where the name of the index object in the database will be index_name. The pattern inside of the indexes can be repeated to created multiple indexes on the same database table object.

fetchArraySize support

As of oracldb 4.1.0 and beyond, rss memory leaks are common. It is due to internal C-based memory allocation of oracledb module. There is no fix provided by the Oracle team that maintains oracledb node module. Hence, this is the best possible solution to combat this memory issue as much as possible.

By default the connector assumes a value of 20 for this parameter. This can be provided in the connector configuration with the same name - fetchArraySize. This will influence how memory is allocated internally by the oracledb node module.

This parameter can be manipulated at the call site, i.e. when a Model.find() is called. It can be passed as options to that api as shown below:

let options = {
  'oe-connector-oracle': {
    fetchArraySize: 10
  }
}

Model.find(filter, options, callback);

However, the final assignment of this fetchArraySize value is based on the lowest of the following:

  1. Limit clause being present in the loopback filter
  2. the fetchArraySize value; it is overriden by means of the call site usage (via options) as shown above, or else, the default value is adopted.

Considering these constraints any findOne() or findById() model api call, the fetchArraySize is assigned 1.

Known issues

  1. It is not possible to specify default value for properties with type “object”