# loopback-connector-oracle > Loopback Oracle Connector Latest version **4.5.2** (published 2020-03-19) · MIT license · 0 weekly downloads ## Install ```sh npm install loopback-connector-oracle pnpm add loopback-connector-oracle yarn add loopback-connector-oracle bun add loopback-connector-oracle ``` ## Health **Score 25/100 (F)** — status: abandoned. Positive: no vulnerabilities. Warnings: low downloads; no types; no esm support. Negative: abandoned. ## Facts | | | |---|---| | Version | 4.5.2 | | Published | 2020-03-19 | | First published | 2013-09-13 | | Weekly downloads | 0 | | License | MIT | | TypeScript types | none | | Module format | CommonJS | | Node | >=8 | | Dependencies | 5 | | Unpacked size | 92.5 KB | | Known vulnerabilities | 0 | | Install scripts | no | | GitHub stars | 28 | | Maintainers | bajtos, dhmlau, emonddr, hacksparrow, jannyhou2016, rfeng, ritch, rmg, schoonology | | Keywords | StrongLoop, LoopBack, Oracle, DataSource, Connector | ## Links - npm: https://www.npmjs.com/package/loopback-connector-oracle - Repository: https://github.com/strongloop/loopback-connector-oracle - Homepage: https://github.com/strongloop/loopback-connector-oracle#readme - Issues: https://github.com/strongloop/loopback-connector-oracle/issues - npm.io page: https://npm.io/package/loopback-connector-oracle ## Dependencies (5) - [async](https://npm.io/package/async.md) ^3.1.0 - [debug](https://npm.io/package/debug.md) ^4.1.1 - [oracledb](https://npm.io/package/oracledb.md) ^4.0.0 - [strong-globalize](https://npm.io/package/strong-globalize.md) ^5.0.0 - [loopback-connector](https://npm.io/package/loopback-connector.md) ^4.10.2 ## Alternatives - [localforage](https://npm.io/package/localforage.md) — 6.2M weekly downloads - [localforage-observable](https://npm.io/package/localforage-observable.md) — 30.8K weekly downloads - [@y/y](https://npm.io/package/@y/y.md) — 30.1K weekly downloads - [@metaobjectsdev/render](https://npm.io/package/@metaobjectsdev/render.md) — 3.5K weekly downloads - [@ledgerhq/coin-algorand](https://npm.io/package/@ledgerhq/coin-algorand.md) — 1.1K weekly downloads ## Recent versions - 4.5.2 (latest) — 2020-03-19 - 4.5.1 — 2019-12-19 - 4.5.0 — 2019-11-01 - 4.4.0 — 2019-09-19 - 4.3.0 — 2019-07-25 - 4.2.0 — 2019-05-22 - 4.1.2 — 2018-12-07 - 4.1.1 — 2018-09-19 - 4.1.0 — 2018-08-07 - 4.0.2 — 2018-07-17 - 4.0.1 — 2018-07-10 - 4.0.0 — 2018-05-23 - 3.2.0 — 2017-04-19 - 3.1.1 — 2017-03-31 - 3.1.0 — 2017-03-07 - … 30 more at https://npm.io/package/loopback-connector-oracle/versions ## README # loopback-connector-oracle [Oracle](https://www.oracle.com/database/index.html) 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](https://github.com/oracle/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](https://github.com/oracle/node-oracledb/blob/master/INSTALL.md#-7-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.html to make sure all the prerequisites are satisfied. In your application root directory, enter this command to install the connector: ```shell $ 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](http://loopback.io/doc/en/lb3/Data-source-generator.html) to add a Oracle data source to your application. The generator will prompt for the database server hostname, port, and other settings required 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: {% include code-caption.html content="/server/datasources.json" %} ```javascript "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](http://docs.oracle.com/cd/E11882_01/network.112/e10836/naming.htm#NETAG008) 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: {% include code-caption.html content="/server/datasources.json" %} ```javascript { "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= ``` Now you can use either the TNS alias or LDAP service name to configure a data source: ```javascript 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, {% include code-caption.html content="/server/datasources.json" %} ```javascript { "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 to `127.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](http://stackoverflow.com/questions/10484231/ora-24408-could-not-generate-unique-server-group-name). ## 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. {% include code-caption.html content="/common/models/model.json" %} ```javascript { "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](http://loopback.io/doc/en/lb3/LoopBack-types.html) for details on LoopBack's data types. ### JSON to Oracle Types
LoopBack Type Oracle Type
String
JSON
Text
default		 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)
VARCHAR
VARCHAR2,
LONG VARCHAR
NCHAR
NVARCHAR2		 String
LONG, BLOB, CLOB, NCLOB Node.js Buffer object
NUMBER
INTEGER
DECIMAL
DOUBLE
FLOAT
BIGINT
SMALLINT
REAL
NUMERIC
BINARY_FLOAT
BINARY_DOUBLE
UROWID
ROWID		 Number
DATE
TIMESTAMP		 Date
## Discovery and auto-migration ### Model discovery The Oracle connector supports _model discovery_ that enables you to create LoopBack models based on an existing database schema using the unified [database discovery API](http://apidocs.strongloop.com/loopback-datasource-juggler/#datasource-prototype-discoverandbuildmodels). For more information on discovery, see [Discovering models from relational databases](https://loopback.io/doc/en/lb3/Discovering-models-from-relational-databases.html). For an example of model discover, see [`example/app.js`](https://github.com/strongloop/loopback-connector-oracle/blob/master/example/app.js). ### Auto-migratiion The Oracle connector also supports _auto-migration_ that enables you to create a database schema from LoopBack models using the [LoopBack automigrate method](http://apidocs.strongloop.com/loopback-datasource-juggler/#datasource-prototype-automigrate). For more information on auto-migration, see [Creating a database schema from models](https://loopback.io/doc/en/lb3/Creating-a-database-schema-from-models.html) 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_TRIGGER Destroying 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 ```bash ORACLE_HOST= ORACLE_PORT= ORACLE_USER= ORACLE_PASSWORD= ORACLE_DATABASE= npm test ``` - Windows ```bash SET ORACLE_HOST= SET ORACLE_PORT= SET ORACLE_USER= SET ORACLE_PASSWORD= SET ORACLE_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](https://docs.docker.com/engine/installation/) installed, run the following script which would spawn an Oracle instance on your local machine: ```bash source setup.sh ``` where ``, ``, ``, and `PASSWORD` are optional parameters. The default values are `localhost`, `1521`, `admin`, and `0raclep4ss` respectively. The `DATABASE` setting is always `XE`. - Run the test: ```bash npm test ``` --- _Source: https://npm.io/package/loopback-connector-oracle · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._