loopback-connector-oracle

The Oracle connector enables LoopBack applications to connect to Oracle data sources.

See also:

Tip: Use the Oracle installer command, lb oracle,to easily install and troubleshoot installing loopback-oracle-installerand the Oracle data source connector.

Note: This page was generated from the loopback-connector-oracle README.

loopback-connector-oracle

Oracle is an object-relational database management system produced by Oracle Corporation. The loopback-connector-oracle module is the Oracle connector for the LoopBack framework based on the node-oracledb module.

For more information, see the LoopBack documentation).

Prerequisites

Node.js: The Oracle connector requires Node.js version 6.x and up.

Windows: On 32-bit Windows systems, you must use the 32-bit version of Node.js. On 64-bit Windows systems, you must use the 64-bit version of Node.js. For more information, see Node-oracledb Installation on Windows.

Oracle: The Oracle connector requires Oracle client libraries 11.2+ and can connect to Oracle Database Server 9.2+.

Installation

Before installing this module, please follow instructions at https://oracle.github.io/node-oracledb/INSTALL.htmlto make sure all the prerequisites are satisfied.

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

$ npm install loopback-connector-oracle --save

If you create a Oracle data source using the data source generator as described below, you don’t have to do this, since the generator will run npm install for you.

The libaio library is required on Linux systems:

On Ubuntu/Debian, get it with this command:

sudo apt-get install libaio1

On Fedora/CentOS/RHEL, get it with this command:

sudo yum install libaio

Creating an Oracle data source

Use the Data source generator to add a Oracle data source to your application.The generator will prompt for the database server hostname, port, and other settingsrequired to connect to a Oracle database. It will also run the npm install command above for you.

The entry in the application’s /server/datasources.json will look like this:

/server/datasources.json

"mydb": {
  "name": "mydb",
  "connector": "oracle",
  "tns": "demo",
  "host": "myserver",
  "port": 3306,
  "database": "mydb",
  "password": "mypassword",
  "user": "admin"
 }

Edit datasources.json to add any other additional properties that you require.

Connector properties

The connector properties depend on naming methods you use for the Oracle database.LoopBack supports three naming methods:

Easy connect: host/port/database.
Local naming (TNS): alias to a full connection string that can specify all the attributes that Oracle supports.
Directory naming (LDAP): directory for looking up the full connection string that can specify all the attributes that Oracle supports.

Easy Connect

Easy Connect is the simplest form that provides out-of-the-box TCP/IP connectivity to databases.The data source then has the following settings.

Property	Type	Default	Description
host or hostname	String	localhost	Host name or IP address of the Oracle database server
port	Number	1521	Port number of the Oracle database server
username or user	String		User name to connect to the Oracle database server
password	String		Password to connect to the Oracle database server
database	String	XE	Oracle database listener name

For example:

/server/datasources.json

{
  "demoDB": {
    "connector": "oracle",
    "host": "oracle-demo.strongloop.com",
    "port": 1521,
    "database": "XE",
    "username": "demo",
    "password": "L00pBack"
  }
}

Local and directory naming

Both local and directory naming require that you place configuration files in a TNS admin directory, such as /oracle/admin.

sqlnet.ora

This specifies the supported naming methods; for example:

NAMES.DIRECTORY_PATH=(LDAP,TNSNAMES,EZCONNECT)

nsnames.ora

This maps aliases to connection stringsl for example:

demo1=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=demo.strongloop.com)(PORT=1521)))

ldap.ora

This configures the LDAP server.

DIRECTORY_SERVERS=(localhost:1389)
DEFAULT_ADMIN_CONTEXT="dc=strongloop,dc=com"
DIRECTORY_SERVER_TYPE=OID

Set up TNS_ADMIN environment variable

For the Oracle connector to pick up the configurations, you must set the environment variable ‘TNS_ADMIN’ to the directory containing the .ora files.

export TNS_ADMIN=<directory containing .ora files>

Now you can use either the TNS alias or LDAP service name to configure a data source:

var ds = loopback.createDataSource({
  "tns": "demo", // The tns property can be a tns name or LDAP service name
  "username": "demo",
  "password": "L00pBack"
});

Connection pooling options

Property name	Description	Default value
minConn	Minimum number of connections in the connection pool	1
maxConn	Maxmimum number of connections in the connection pool	10
incrConn	Incremental number of connections for the connection pool.	1
timeout	Time-out period in seconds for a connection in the connection pool. The Oracle connector will terminate connections in this connection pool that are idle longer than the time-out period.	10

For example,

/server/datasources.json

{
  "demoDB": {
    "connector": "oracle",
    "minConn":1,
    "maxConn":5,
    "incrConn":1,
    "timeout": 10,
    ...
  }
}

Connection troubleshooting

If you encounter this error:

Error: ORA-24408: could not generate unique server group name

Then the Oracle 11g client requires an entry with your hostname pointing to127.0.0.1.

To resolve:

Get your hostname. Check your hostname by running this command (for example, if your machine’s name is “earth”):

$ hostname
earth

Update /etc/hosts and map 127.0.0.1 to your hostname “earth”:

...
127.0.0.1 localhost earth
...

Verify the fix. Run the example in examples/app.js:

$ node examples/app.js

For more information, see StackOverflow question.

Model properties

An Oracle model definition consists of the following properties:

name: Name of the model, by default, it’s the camel case of the table.
options: Model-level operations and mapping to Oracle schema/table.
properties: Property definitions, including mapping to Oracle column.

/common/models/model.json

{
    "name":"Inventory",
    "options":{
      "idInjection":false,
      "oracle":{
        "schema":"STRONGLOOP",
        "table":"INVENTORY"
      }
    },
    "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,
        "oracle":{
          "columnName":"LOCATION_ID",
          "dataType":"VARCHAR2",
          "dataLength":20,
          "nullable":"N"
        }
      },
      "available":{
        "type":"Number",
        "required":false,
        "length":22,
        "oracle":{
          "columnName":"AVAILABLE",
          "dataType":"NUMBER",
          "dataLength":22,
          "nullable":"Y"
        }
      },
      "total":{
        "type":"Number",
        "required":false,
        "length":22,
        "oracle":{
          "columnName":"TOTAL",
          "dataType":"NUMBER",
          "dataLength":22,
          "nullable":"Y"
        }
      }
    }
  }

Type mapping

See LoopBack types for details on LoopBack’s data types.

JSON to Oracle Types

LoopBack Type	Oracle Type
StringJSONTextdefault	VARCHAR2 Default length is 1024
Number	NUMBER
Date	DATE
Timestamp	TIMESTAMP(3)
Boolean	CHAR(1)

Oracle Types to JSON

Oracle Type	LoopBack Type
CHAR(1)	Boolean
CHAR(n)VARCHARVARCHAR2,LONG VARCHARNCHARNVARCHAR2	String
LONG, BLOB, CLOB, NCLOB	Node.js Buffer object
NUMBERINTEGERDECIMALDOUBLEFLOATBIGINTSMALLINTREALNUMERICBINARY_FLOATBINARY_DOUBLEUROWIDROWID	Number
DATETIMESTAMP	Date

Discovery and auto-migration

Model discovery

The Oracle connector supports model discovery that enables you to create LoopBack modelsbased on an existing database schema using the unified database discovery API. For more information on discovery, see Discovering models from relational databases.

For an example of model discover, see example/app.js.

Auto-migratiion

The Oracle connector also supports auto-migration that enables you to create a database schemafrom LoopBack models using the LoopBack automigrate method.

For more information on auto-migration, see Creating a database schema from models for more information.

LoopBack Oracle connector creates the following schema objects for a given model:

A table, for example, PRODUCT
A sequence for the primary key, for example, PRODUCT_ID_SEQUENCE
A trigger to generate the primary key from the sequnce, for example, PRODUCT_ID_TRIGGERDestroying models may result in errors due to foreign key integrity. First delete any related models by calling delete on models with relationships.

Running tests

Own instance

If you have a local or remote Oracle instance and would like to use that to run the test suite, use the following command:

Linux

ORACLE_HOST=<HOST> ORACLE_PORT=<PORT> ORACLE_USER=<USER> ORACLE_PASSWORD=<PASSWORD> ORACLE_DATABASE=<DATABASE> npm test

Windows

SET ORACLE_HOST=<HOST>
SET ORACLE_PORT=<PORT>
SET ORACLE_USER=<USER>
SET ORACLE_PASSWORD=<PASSWORD>
SET ORACLE_DATABASE=<DATABASE>
npm test

Docker

If you do not have a local Oracle instance, you can also run the test suite with very minimal requirements.

Assuming you have Docker installed, run the following script which would spawn an Oracle instance on your local machine:

source setup.sh <HOST> <PORT>

where <HOST>, <PORT>, <USER>, and PASSWORD are optional parameters. The default values are localhost, 1521, admin, and 0raclep4ss respectively. The DATABASE setting is always XE.

Run the test:

npm test

Tags: connectors