1 | /* Copyright (c) 2018, 2023, Oracle and/or its affiliates. */
|
2 |
|
3 | /******************************************************************************
|
4 | *
|
5 | * This software is dual-licensed to you under the Universal Permissive License
|
6 | * (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
|
7 | * 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
|
8 | * either license.
|
9 | *
|
10 | * If you elect to accept the software under the Apache License, Version 2.0,
|
11 | * the following applies:
|
12 | *
|
13 | * Licensed under the Apache License, Version 2.0 (the "License");
|
14 | * you may not use this file except in compliance with the License.
|
15 | * You may obtain a copy of the License at
|
16 | *
|
17 | * https://www.apache.org/licenses/LICENSE-2.0
|
18 | *
|
19 | * Unless required by applicable law or agreed to in writing, software
|
20 | * distributed under the License is distributed on an "AS IS" BASIS,
|
21 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
22 | * See the License for the specific language governing permissions and
|
23 | * limitations under the License.
|
24 | *
|
25 | * NAME
|
26 | * example.js
|
27 | *
|
28 | * DESCRIPTION
|
29 | * A basic node-oracledb example.
|
30 | *
|
31 | * For connection pool examples see connectionpool.js and webapp.js
|
32 | * For a ResultSet example see resultset1.js
|
33 | * For a query stream example see selectstream.js
|
34 | *
|
35 | *****************************************************************************/
|
36 |
|
37 | ;
|
38 |
|
39 | Error.stackTraceLimit = 50;
|
40 |
|
41 | const oracledb = require('oracledb');
|
42 | const dbConfig = require('./dbconfig.js');
|
43 |
|
44 | // This example runs in both node-oracledb Thin and Thick modes.
|
45 | //
|
46 | // Optionally run in node-oracledb Thick mode
|
47 | if (process.env.NODE_ORACLEDB_DRIVER_MODE === 'thick') {
|
48 |
|
49 | // Thick mode requires Oracle Client or Oracle Instant Client libraries.
|
50 | // On Windows and macOS Intel you can specify the directory containing the
|
51 | // libraries at runtime or before Node.js starts. On other platforms (where
|
52 | // Oracle libraries are available) the system library search path must always
|
53 | // include the Oracle library path before Node.js starts. If the search path
|
54 | // is not correct, you will get a DPI-1047 error. See the node-oracledb
|
55 | // installation documentation.
|
56 | let clientOpts = {};
|
57 | // On Windows and macOS Intel platforms, set the environment
|
58 | // variable NODE_ORACLEDB_CLIENT_LIB_DIR to the Oracle Client library path
|
59 | if (process.platform === 'win32' || (process.platform === 'darwin' && process.arch === 'x64')) {
|
60 | clientOpts = { libDir: process.env.NODE_ORACLEDB_CLIENT_LIB_DIR };
|
61 | }
|
62 | oracledb.initOracleClient(clientOpts); // enable node-oracledb Thick mode
|
63 | }
|
64 |
|
65 | console.log(oracledb.thin ? 'Running in thin mode' : 'Running in thick mode');
|
66 |
|
67 | async function run() {
|
68 | let connection;
|
69 |
|
70 | try {
|
71 |
|
72 | let sql, binds, options, result;
|
73 |
|
74 | connection = await oracledb.getConnection(dbConfig);
|
75 |
|
76 | //
|
77 | // Create a table
|
78 | //
|
79 |
|
80 | const stmts = [
|
81 | `DROP TABLE no_example`,
|
82 |
|
83 | `CREATE TABLE no_example (id NUMBER, data VARCHAR2(20))`
|
84 | ];
|
85 |
|
86 | for (const s of stmts) {
|
87 | try {
|
88 | await connection.execute(s);
|
89 | } catch (e) {
|
90 | if (e.errorNum != 942)
|
91 | console.error(e);
|
92 | }
|
93 | }
|
94 |
|
95 | //
|
96 | // Insert three rows
|
97 | //
|
98 |
|
99 | sql = `INSERT INTO no_example VALUES (:1, :2)`;
|
100 |
|
101 | binds = [
|
102 | [101, "Alpha" ],
|
103 | [102, "Beta" ],
|
104 | [103, "Gamma" ]
|
105 | ];
|
106 |
|
107 | // For a complete list of options see the documentation.
|
108 | options = {
|
109 | autoCommit: true,
|
110 | // batchErrors: true, // continue processing even if there are data errors
|
111 | bindDefs: [
|
112 | { type: oracledb.NUMBER },
|
113 | { type: oracledb.STRING, maxSize: 20 }
|
114 | ]
|
115 | };
|
116 |
|
117 | result = await connection.executeMany(sql, binds, options);
|
118 |
|
119 | console.log("Number of rows inserted:", result.rowsAffected);
|
120 |
|
121 | //
|
122 | // Query the data
|
123 | //
|
124 |
|
125 | sql = `SELECT * FROM no_example`;
|
126 |
|
127 | binds = {};
|
128 |
|
129 | // For a complete list of options see the documentation.
|
130 | options = {
|
131 | outFormat: oracledb.OUT_FORMAT_OBJECT, // query result format
|
132 | // fetchArraySize: 100 // internal buffer allocation size for tuning
|
133 | };
|
134 |
|
135 | result = await connection.execute(sql, binds, options);
|
136 |
|
137 | // Column metadata can be shown, if desired
|
138 | // console.log("Metadata: ");
|
139 | // console.dir(result.metaData, { depth: null });
|
140 |
|
141 | console.log("Query results: ");
|
142 | console.dir(result.rows, { depth: null });
|
143 |
|
144 | } catch (err) {
|
145 | console.error(err);
|
146 | } finally {
|
147 | if (connection) {
|
148 | try {
|
149 | await connection.close();
|
150 | } catch (err) {
|
151 | console.error(err);
|
152 | }
|
153 | }
|
154 | }
|
155 | }
|
156 |
|
157 | run();
|