0
# OracleDB
1

2
OracleDB is a comprehensive Node.js native add-on module that enables high-performance Oracle Database connectivity for JavaScript and TypeScript applications. It supports both 'Thin' mode (direct database connection without Oracle Client libraries) and 'Thick' mode (with Oracle Client libraries for advanced features), offering complete Oracle Database functionality including SQL, PL/SQL, JSON, and SODA operations.
3

4
## Package Information
5

6
- **Package Name**: oracledb
7
- **Package Type**: npm
8
- **Language**: JavaScript/TypeScript
9
- **Installation**: `npm install oracledb`
10

11
## Core Imports
12

13
```javascript
14
const oracledb = require('oracledb');
15
```
16

17
For ES modules:
18

19
```javascript
20
import oracledb from 'oracledb';
21
```
22

23
## Basic Usage
24

25
```javascript
26
const oracledb = require('oracledb');
27

28
// Configure global settings
29
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
30
oracledb.autoCommit = true;
31

32
// Create a connection
33
const connection = await oracledb.getConnection({
34
  user: "hr",
35
  password: "welcome123",
36
  connectString: "localhost:1521/XE"
37
});
38

39
// Execute a query
40
const result = await connection.execute(
41
  `SELECT department_id, department_name 
42
   FROM departments 
43
   WHERE location_id = :loc_id`,
44
  [1700]
45
);
46

47
console.log(result.rows);
48

49
// Close the connection
50
await connection.close();
51
```
52

53
## Architecture
54

55
OracleDB is built around several key components:
56

57
- **Connection Management**: Direct connections and connection pooling for scalable applications
58
- **SQL Execution**: Complete SQL, PL/SQL, and DDL support with parameter binding and result handling
59
- **Data Type System**: Native Oracle data type support including LOBs, JSON, and custom objects
60
- **Advanced Queuing (AQ)**: Oracle Advanced Queuing for message-based applications
61
- **SODA**: Simple Oracle Document Access for JSON document storage and querying
62
- **Transaction Control**: Full transaction management including distributed transactions (TPC/XA)
63
- **Monitoring**: Connection health, performance statistics, and tracing capabilities
64

65
## Capabilities
66

67
### Connection Management
68

69
Core database connectivity including single connections and connection pools for scalable applications.
70

71
```javascript { .api }
72
// Create a single connection
73
function getConnection(config: ConnectionAttributes): Promise<Connection>;
74

75
// Create a connection pool  
76
function createPool(config: PoolAttributes): Promise<Pool>;
77

78
// Get an existing pool
79
function getPool(alias?: string): Pool;
80
```
81

82
[Connection Management](./connection-management.md)
83

84
### SQL Execution
85

86
Execute SQL statements, stored procedures, and PL/SQL blocks with full parameter binding and result handling.
87

88
```javascript { .api }
89
// Execute SQL with optional binds and options
90
execute(sql: string, binds?: BindParameters, options?: ExecuteOptions): Promise<Result>;
91

92
// Execute statement multiple times
93
executeMany(sql: string, binds: BindParameters[], options?: ExecuteManyOptions): Promise<ResultSet>;
94
```
95

96
[SQL Execution](./sql-execution.md)
97

98
### Connection Pools
99

100
Advanced connection pooling for high-performance applications with automatic connection management.
101

102
```javascript { .api }
103
interface Pool {
104
  getConnection(options?: GetConnectionOptions): Promise<Connection>;
105
  close(drainTime?: number): Promise<void>;
106
  reconfigure(options: PoolAttributes): Promise<void>;
107
}
108
```
109

110
[Connection Pools](./connection-pools.md)
111

112
### Data Types and LOBs
113

114
Oracle-specific data types including Large Objects (LOBs), JSON, and custom database objects.
115

116
```javascript { .api }
117
interface Lob {
118
  close(): Promise<void>;
119
  getData(): Promise<string | Buffer>;
120
  read(size?: number): Promise<string | Buffer>;
121
  write(data: string | Buffer): Promise<void>;
122
}
123
```
124

125
[Data Types and LOBs](./data-types-lobs.md)
126

127
### Advanced Queuing (AQ)
128

129
Oracle Advanced Queuing for reliable message-based communication between applications.
130

131
```javascript { .api }
132
interface AqQueue {
133
  deqOne(options?: AqDeqOptions): Promise<AqMessage>;
134
  deqMany(maxMessages: number, options?: AqDeqOptions): Promise<AqMessage[]>;
135
  enqOne(message: AqMessage, options?: AqEnqOptions): Promise<void>;
136
  enqMany(messages: AqMessage[], options?: AqEnqOptions): Promise<void>;
137
}
138
```
139

140
[Advanced Queuing](./advanced-queuing.md)
141

142
### SODA (Simple Oracle Document Access)
143

144
JSON document storage and querying capabilities for building document-oriented applications.
145

146
```javascript { .api }
147
interface SodaDatabase {
148
  createCollection(name: string, options?: SodaCollectionOptions): Promise<SodaCollection>;
149
  openCollection(name: string): Promise<SodaCollection>;
150
  getCollectionNames(options?: GetCollectionNamesOptions): Promise<string[]>;
151
}
152
```
153

154
[SODA Operations](./soda-operations.md)
155

156
### Transaction Management
157

158
Transaction control including commit, rollback, and distributed transaction support.
159

160
```javascript { .api }
161
// Transaction control
162
commit(): Promise<void>;
163
rollback(): Promise<void>;
164

165
// Distributed transactions (TPC/XA)
166
tpcBegin(xid: string, flags?: number, timeout?: number): Promise<void>;
167
tpcCommit(xid: string, onePhase?: boolean): Promise<boolean>;
168
tpcRollback(xid: string): Promise<void>;
169
```
170

171
[Transaction Management](./transaction-management.md)
172

173
### Database Administration
174

175
Database startup, shutdown, and configuration management operations.
176

177
```javascript { .api }
178
// Database lifecycle management
179
function startup(connAttr: ConnectionAttributes, startupAttr?: StartupAttributes): Promise<void>;
180
function shutdown(connAttr: ConnectionAttributes, shutdownMode?: number): Promise<void>;
181

182
// Network service management
183
function getNetworkServiceNames(configDir?: string): Promise<string[]>;
184
```
185

186
### Extension and Hook Management
187

188
Register hooks and providers for connection processing and configuration.
189

190
```javascript { .api }
191
// Hook registration
192
function registerProcessConfigurationHook(fn: ProcessConfigurationHook): void;
193
function registerConfigurationProviderHook(configProvider: string, fn: ConfigurationProviderHook): void;
194
```
195

196
### Configuration and Settings
197

198
Global configuration options and runtime settings for optimal performance and behavior.
199

200
```javascript { .api }
201
// Global settings (getters/setters)
202
oracledb.autoCommit: boolean;
203
oracledb.fetchArraySize: number;
204
oracledb.maxRows: number;
205
oracledb.outFormat: number;
206
oracledb.poolMax: number;
207
oracledb.poolMin: number;
208
```
209

210
[Configuration and Settings](./configuration-settings.md)
211

212
## Types
213

214
```javascript { .api }
215
interface ConnectionAttributes {
216
  user?: string;
217
  password?: string;
218
  newPassword?: string;
219
  accessToken?: string | AccessTokenCallback;
220
  connectString?: string;
221
  connectionClass?: string;
222
  privilege?: number;
223
  externalAuth?: boolean;
224
  stmtCacheSize?: number;
225
  edition?: string;
226
  events?: boolean;
227
  tag?: string;
228
  shardingKey?: (string | number | Date)[];
229
  superShardingKey?: (string | number | Date)[];
230
}
231

232
interface PoolAttributes extends ConnectionAttributes {
233
  poolAlias?: string;
234
  poolIncrement?: number;
235
  poolMax?: number;
236
  poolMaxPerShard?: number;
237
  poolMin?: number;
238
  poolPingInterval?: number;
239
  poolPingTimeout?: number;
240
  poolTimeout?: number;
241
  queueMax?: number;
242
  queueTimeout?: number;
243
  sessionCallback?: string | SessionCallback;
244
  sodaMetaDataCache?: boolean;
245
  enableStatistics?: boolean;
246
}
247

248
interface ExecuteOptions {
249
  autoCommit?: boolean;
250
  fetchArraySize?: number;
251
  fetchInfo?: {[key: string]: FetchInfo};
252
  fetchTypeHandler?: FetchTypeHandler;
253
  keepInStmtCache?: boolean;
254
  maxRows?: number;
255
  outFormat?: number;
256
  prefetchRows?: number;
257
  resultSet?: boolean;
258
}
259

260
type BindParameters = BindParametersObject | BindParametersArray;
261
type BindParametersObject = {[key: string]: BindValue};
262
type BindParametersArray = BindValue[];
263

264
interface BindValue {
265
  val?: any;
266
  dir?: number;
267
  type?: number;
268
  maxSize?: number;
269
  maxArraySize?: number;
270
}
271

272
interface StartupAttributes {
273
  force?: boolean;
274
  restrict?: boolean;
275
  pfile?: string;
276
}
277

278
type ProcessConfigurationHook = (options: ConnectionAttributes | PoolAttributes) => Promise<void>;
279
type ConfigurationProviderHook = (args: ConfigProviderArgs) => Promise<[any, any?]>;
280

281
interface ConfigProviderArgs {
282
  provider_arg?: string;
283
  urlExtendedPart?: string;
284
  credential?: any;
285
  paramMap?: Map<string, any>;
286
}
287

288
type AccessTokenCallback = (refresh: boolean, config?: any) => Promise<string | AccessToken>;
289
type SessionCallback = (connection: Connection, requestedTag: string, actualTag: string) => Promise<void>;
290
type FetchTypeHandler = (metaData: ColumnMetaData) => any;
291

292
interface AccessToken {
293
  token: string;
294
  privateKey?: string;
295
}
296

297
interface ColumnMetaData {
298
  name: string;
299
  fetchType?: number;
300
  dbType: number;
301
  byteSize: number;
302
  precision?: number;
303
  scale?: number;
304
  nullable: boolean;
305
}
306

307
interface FetchInfo {
308
  type?: number;
309
  converter?: (value: any) => any;
310
}
311

312
interface SodaCollectionOptions {
313
  metaData?: any;
314
  mode?: number;
315
}
316

317
interface SodaDocumentOptions {
318
  key?: string;
319
  mediaType?: string;
320
}
321

322
interface GetCollectionNamesOptions {
323
  startsWith?: string;
324
  limit?: number;
325
}
326

327
type SodaDocumentArray = SodaDocument[];
328
```
329

330
## Classes
331

332
### Data Type Classes
333

334
```javascript { .api }
335
// JSON ID class for SODA document keys
336
class JsonId extends Uint8Array {
337
  toJSON(): string;
338
}
339

340
// Sparse vector representation for vector data types
341
class SparseVector {
342
  constructor(input?: { indices: number[], values: number[], numDimensions: number });
343
  indices: Uint32Array;  
344
  values: Float64Array;
345
  numDimensions: number;
346
  toJSON(): any;
347
}
348

349
// Interval Year-to-Month representation
350
class IntervalYM {
351
  constructor(obj?: { years?: number, months?: number });
352
  years: number;
353
  months: number;
354
}
355

356
// Interval Day-to-Second representation  
357
class IntervalDS {
358
  constructor(obj?: { days?: number, hours?: number, minutes?: number, seconds?: number, fseconds?: number });
359
  days: number;
360
  hours: number;
361
  minutes: number;
362
  seconds: number;
363
  fseconds: number;
364
}
365

366
// Base database object class
367
class BaseDbObject {
368
  [key: string]: any;
369
}
370
```
371

372
### Advanced Queuing Classes
373

374
```javascript { .api }
375
// AQ Dequeue options configuration
376
class AqDeqOptions {
377
  condition?: string;
378
  consumerName?: string;
379
  correlation?: string;
380
  mode?: number;
381
  msgId?: Buffer;
382
  navigation?: number;
383
  transformation?: string;
384
  visibility?: number;
385
  wait?: number;
386
}
387

388
// AQ Enqueue options configuration
389
class AqEnqOptions {
390
  deliveryMode?: number;
391
  transformation?: string;
392
  visibility?: number;
393
}
394

395
// AQ Message representation
396
class AqMessage {
397
  correlation?: string;
398
  delay?: number;
399
  deliveryMode?: number;
400
  exceptionQueue?: string;
401
  expiration?: number;
402
  msgId?: Buffer;
403
  payload?: any;
404
  priority?: number;
405
  recipients?: string[];
406
}
407

408
// AQ Queue interface
409
class AqQueue {
410
  name: string;
411
  deqOptions: AqDeqOptions;
412
  enqOptions: AqEnqOptions;
413
  payloadType?: number;
414
  payloadTypeName?: string;
415
  
416
  deqOne(options?: AqDeqOptions): Promise<AqMessage>;
417
  deqMany(maxMessages: number, options?: AqDeqOptions): Promise<AqMessage[]>;
418
  enqOne(message: AqMessage, options?: AqEnqOptions): Promise<void>;
419
  enqMany(messages: AqMessage[], options?: AqEnqOptions): Promise<void>;
420
}
421
```
422

423
### SODA Classes
424

425
```javascript { .api }
426
// SODA Database interface
427
class SodaDatabase {
428
  createCollection(name: string, options?: SodaCollectionOptions): Promise<SodaCollection>;
429
  createDocument(content: any, options?: SodaDocumentOptions): SodaDocument;
430
  getCollectionNames(options?: GetCollectionNamesOptions): Promise<string[]>;
431
  openCollection(name: string): Promise<SodaCollection>;
432
}
433

434
// SODA Collection interface
435
class SodaCollection {
436
  metaData: any;
437
  name: string;
438
  
439
  createIndex(indexSpec: any): Promise<void>;
440
  drop(): Promise<{ dropped: boolean }>;
441
  dropIndex(indexName: string, options?: { force?: boolean }): Promise<void>;
442
  find(): SodaOperation;
443
  getDataGuide(): Promise<SodaDocument>;
444
  insertMany(documents: (SodaDocument | any)[]): Promise<SodaDocumentArray>;
445
  insertManyAndGet(documents: (SodaDocument | any)[]): Promise<SodaDocumentArray>;
446
  insertOne(document: SodaDocument | any): Promise<void>;
447
  insertOneAndGet(document: SodaDocument | any): Promise<SodaDocument>;
448
  save(document: SodaDocument): Promise<void>;
449
  saveAndGet(document: SodaDocument): Promise<SodaDocument>;
450
}
451

452
// SODA Document representation
453
class SodaDocument {
454
  createdOn?: string;
455
  key?: string;
456
  lastModified?: string;
457
  mediaType?: string;
458
  version?: string;
459
  
460
  getContent(): any;
461
  getContentAsBuffer(): Buffer;
462
  getContentAsString(): string;
463
}
464

465
// SODA Document cursor for iteration
466
class SodaDocCursor {
467
  getNext(): Promise<SodaDocument>;
468
  close(): Promise<void>;
469
}
470

471
// SODA Operation builder for find operations
472
class SodaOperation {
473
  count(): Promise<{ count: number }>;
474
  filter(filterSpec: any): SodaOperation;
475
  getCursor(): Promise<SodaDocCursor>;
476
  getDocuments(): Promise<SodaDocument[]>;
477
  getOne(): Promise<SodaDocument>;
478
  hint(hint: string): SodaOperation;
479
  key(key: string): SodaOperation;
480
  keys(keys: string[]): SodaOperation;
481
  limit(limit: number): SodaOperation; 
482
  remove(): Promise<{ count: number }>;
483
  replaceOne(document: SodaDocument | any): Promise<{ replaced: boolean }>;
484
  replaceOneAndGet(document: SodaDocument | any): Promise<SodaDocument>;
485
  skip(skip: number): SodaOperation;
486
  version(version: string): SodaOperation;
487
}
488
```
489

490
### Pool Statistics Class
491

492
```javascript { .api }
493
// Pool connection statistics
494
class PoolStatistics {
495
  connectionsInUse: number;
496
  connectionsOpen: number;
497
  poolAlias: string;
498
  poolMax: number;
499
  poolMaxPerShard: number;
500
  poolMin: number;
501
  queueLength: number;
502
  queueMax: number; 
503
  queueTimeout: number;
504
  requestsDequeued: number;
505
  requestsEnqueued: number;
506
  requestsNeverQueued: number;
507
  sessionTimeouts: number;
508
  shard: any[];
509
  stmtCacheHits: number;
510
  stmtCacheMisses: number;
511
}
512
```
513

514
## Constants
515

516
```javascript { .api }
517
// CQN Operation Codes
518
const CQN_OPCODE_ALL_OPS: 0;
519
const CQN_OPCODE_ALL_ROWS: 1;
520
const CQN_OPCODE_ALTER: 16;
521
const CQN_OPCODE_DELETE: 8;
522
const CQN_OPCODE_DROP: 32;
523
const CQN_OPCODE_INSERT: 2;
524
const CQN_OPCODE_UPDATE: 4;
525

526
// Statement Types
527
const STMT_TYPE_UNKNOWN: 0;
528
const STMT_TYPE_SELECT: 1;
529
const STMT_TYPE_UPDATE: 2;
530
const STMT_TYPE_DELETE: 3;
531
const STMT_TYPE_INSERT: 4;
532
const STMT_TYPE_CREATE: 5;
533
const STMT_TYPE_DROP: 6;
534
const STMT_TYPE_ALTER: 7;
535
const STMT_TYPE_BEGIN: 8;
536
const STMT_TYPE_DECLARE: 9;
537
const STMT_TYPE_CALL: 10;
538
const STMT_TYPE_EXPLAIN_PLAN: 15;
539
const STMT_TYPE_MERGE: 16;
540
const STMT_TYPE_ROLLBACK: 17;
541
const STMT_TYPE_COMMIT: 21;
542

543
// Shutdown Modes
544
const SHUTDOWN_MODE_DEFAULT: 0;
545
const SHUTDOWN_MODE_TRANSACTIONAL: 1;
546
const SHUTDOWN_MODE_TRANSACTIONAL_LOCAL: 2;
547
const SHUTDOWN_MODE_IMMEDIATE: 3;
548
const SHUTDOWN_MODE_ABORT: 4;
549
const SHUTDOWN_MODE_FINAL: 5;
550

551
// Startup Modes
552
const STARTUP_MODE_DEFAULT: 0;
553
const STARTUP_MODE_FORCE: 1;
554
const STARTUP_MODE_RESTRICT: 2;
555

556
// Subscription Event Types
557
const SUBSCR_EVENT_TYPE_SHUTDOWN: 2;
558
const SUBSCR_EVENT_TYPE_SHUTDOWN_ANY: 3;
559
const SUBSCR_EVENT_TYPE_STARTUP: 1;
560
const SUBSCR_EVENT_TYPE_DEREG: 5;
561
const SUBSCR_EVENT_TYPE_OBJ_CHANGE: 6;
562
const SUBSCR_EVENT_TYPE_QUERY_CHANGE: 7;
563
const SUBSCR_EVENT_TYPE_AQ: 100;
564

565
// Subscription Grouping Classes
566
const SUBSCR_GROUPING_CLASS_TIME: 1;
567

568
// Subscription Grouping Types  
569
const SUBSCR_GROUPING_TYPE_SUMMARY: 1;
570
const SUBSCR_GROUPING_TYPE_LAST: 2;
571

572
// Subscription Namespaces
573
const SUBSCR_NAMESPACE_AQ: 1;
574
const SUBSCR_NAMESPACE_DBCHANGE: 2;
575

576
// Subscription QoS Flags
577
const SUBSCR_QOS_BEST_EFFORT: 0x10;
578
const SUBSCR_QOS_DEREG_NFY: 0x02;
579
const SUBSCR_QOS_QUERY: 0x08;
580
const SUBSCR_QOS_RELIABLE: 0x01;
581
const SUBSCR_QOS_ROWIDS: 0x04;
582

583
// Privileges
584
const SYSASM: 0x00008000;
585
const SYSBACKUP: 0x00020000;
586
const SYSDBA: 0x00000002;
587
const SYSDG: 0x00040000;
588
const SYSKM: 0x00080000;
589
const SYSOPER: 0x00000004;
590
const SYSPRELIM: 0x00000008;
591
const SYSRAC: 0x00100000;
592

593
// Bind Directions
594
const BIND_IN: 3001;
595
const BIND_INOUT: 3002;
596
const BIND_OUT: 3003;
597

598
// Output Formats
599
const OUT_FORMAT_ARRAY: 4001;
600
const OUT_FORMAT_OBJECT: 4002;
601

602
// SODA Collection Creation Modes
603
const SODA_COLL_MAP_MODE: 5001;
604

605
// Pool Statuses
606
const POOL_STATUS_OPEN: 6000;
607
const POOL_STATUS_DRAINING: 6001;
608
const POOL_STATUS_CLOSED: 6002;
609
const POOL_STATUS_RECONFIGURING: 6003;
610

611
// AQ Dequeue Wait Options
612
const AQ_DEQ_NO_WAIT: 0;
613
const AQ_DEQ_WAIT_FOREVER: 4294967295;
614

615
// AQ Dequeue Modes
616
const AQ_DEQ_MODE_BROWSE: 1;
617
const AQ_DEQ_MODE_LOCKED: 2;
618
const AQ_DEQ_MODE_REMOVE: 3;
619
const AQ_DEQ_MODE_REMOVE_NO_DATA: 4;
620

621
// AQ Dequeue Navigation Flags
622
const AQ_DEQ_NAV_FIRST_MSG: 1;
623
const AQ_DEQ_NAV_NEXT_TRANSACTION: 2;
624
const AQ_DEQ_NAV_NEXT_MSG: 3;
625

626
// AQ Message Delivery Modes
627
const AQ_MSG_DELIV_MODE_PERSISTENT: 1;
628
const AQ_MSG_DELIV_MODE_BUFFERED: 2;
629
const AQ_MSG_DELIV_MODE_PERSISTENT_OR_BUFFERED: 3;
630

631
// AQ Message States
632
const AQ_MSG_STATE_READY: 0;
633
const AQ_MSG_STATE_WAITING: 1;
634
const AQ_MSG_STATE_PROCESSED: 2;
635
const AQ_MSG_STATE_EXPIRED: 3;
636

637
// AQ Visibility Flags
638
const AQ_VISIBILITY_IMMEDIATE: 1;
639
const AQ_VISIBILITY_ON_COMMIT: 2;
640

641
// TPC/XA Begin Flags
642
const TPC_BEGIN_JOIN: 0x00000002;
643
const TPC_BEGIN_NEW: 0x00000001;
644
const TPC_BEGIN_PROMOTE: 0x00000008;
645
const TPC_BEGIN_RESUME: 0x00000004;
646

647
// TPC/XA Two-Phase Commit Flags
648
const TPC_END_NORMAL: 0;
649
const TPC_END_SUSPEND: 0x00100000;
650

651
// Vector Formats
652
const VECTOR_FORMAT_FLOAT32: 2;
653
const VECTOR_FORMAT_FLOAT64: 3;
654
const VECTOR_FORMAT_INT8: 4;
655
const VECTOR_FORMAT_BINARY: 5;
656
```