or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-queuing.mdconfiguration-settings.mdconnection-management.mdconnection-pools.mddata-types-lobs.mdindex.mdsoda-operations.mdsql-execution.mdtransaction-management.md
tile.json

tessl/npm-oracledb

A Node.js module for Oracle Database access from JavaScript and TypeScript

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
npmpkg:npm/oracledb@6.9.x

To install, run

npx @tessl/cli install tessl/npm-oracledb@6.9.0

index.mddocs/

OracleDB

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.

Package Information

  • Package Name: oracledb
  • Package Type: npm
  • Language: JavaScript/TypeScript
  • Installation: npm install oracledb

Core Imports

const oracledb = require('oracledb');

For ES modules:

import oracledb from 'oracledb';

Basic Usage

const oracledb = require('oracledb');

// Configure global settings
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
oracledb.autoCommit = true;

// Create a connection
const connection = await oracledb.getConnection({
  user: "hr",
  password: "welcome123",
  connectString: "localhost:1521/XE"
});

// Execute a query
const result = await connection.execute(
  `SELECT department_id, department_name 
   FROM departments 
   WHERE location_id = :loc_id`,
  [1700]
);

console.log(result.rows);

// Close the connection
await connection.close();

Architecture

OracleDB is built around several key components:

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

Capabilities

Connection Management

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

// Create a single connection
function getConnection(config: ConnectionAttributes): Promise<Connection>;

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

// Get an existing pool
function getPool(alias?: string): Pool;

Connection Management

SQL Execution

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

// Execute SQL with optional binds and options
execute(sql: string, binds?: BindParameters, options?: ExecuteOptions): Promise<Result>;

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

SQL Execution

Connection Pools

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

interface Pool {
  getConnection(options?: GetConnectionOptions): Promise<Connection>;
  close(drainTime?: number): Promise<void>;
  reconfigure(options: PoolAttributes): Promise<void>;
}

Connection Pools

Data Types and LOBs

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

interface Lob {
  close(): Promise<void>;
  getData(): Promise<string | Buffer>;
  read(size?: number): Promise<string | Buffer>;
  write(data: string | Buffer): Promise<void>;
}

Data Types and LOBs

Advanced Queuing (AQ)

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

interface AqQueue {
  deqOne(options?: AqDeqOptions): Promise<AqMessage>;
  deqMany(maxMessages: number, options?: AqDeqOptions): Promise<AqMessage[]>;
  enqOne(message: AqMessage, options?: AqEnqOptions): Promise<void>;
  enqMany(messages: AqMessage[], options?: AqEnqOptions): Promise<void>;
}

Advanced Queuing

SODA (Simple Oracle Document Access)

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

interface SodaDatabase {
  createCollection(name: string, options?: SodaCollectionOptions): Promise<SodaCollection>;
  openCollection(name: string): Promise<SodaCollection>;
  getCollectionNames(options?: GetCollectionNamesOptions): Promise<string[]>;
}

SODA Operations

Transaction Management

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

// Transaction control
commit(): Promise<void>;
rollback(): Promise<void>;

// Distributed transactions (TPC/XA)
tpcBegin(xid: string, flags?: number, timeout?: number): Promise<void>;
tpcCommit(xid: string, onePhase?: boolean): Promise<boolean>;
tpcRollback(xid: string): Promise<void>;

Transaction Management

Database Administration

Database startup, shutdown, and configuration management operations.

// Database lifecycle management
function startup(connAttr: ConnectionAttributes, startupAttr?: StartupAttributes): Promise<void>;
function shutdown(connAttr: ConnectionAttributes, shutdownMode?: number): Promise<void>;

// Network service management
function getNetworkServiceNames(configDir?: string): Promise<string[]>;

Extension and Hook Management

Register hooks and providers for connection processing and configuration.

// Hook registration
function registerProcessConfigurationHook(fn: ProcessConfigurationHook): void;
function registerConfigurationProviderHook(configProvider: string, fn: ConfigurationProviderHook): void;

Configuration and Settings

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

// Global settings (getters/setters)
oracledb.autoCommit: boolean;
oracledb.fetchArraySize: number;
oracledb.maxRows: number;
oracledb.outFormat: number;
oracledb.poolMax: number;
oracledb.poolMin: number;

Configuration and Settings

Types

interface ConnectionAttributes {
  user?: string;
  password?: string;
  newPassword?: string;
  accessToken?: string | AccessTokenCallback;
  connectString?: string;
  connectionClass?: string;
  privilege?: number;
  externalAuth?: boolean;
  stmtCacheSize?: number;
  edition?: string;
  events?: boolean;
  tag?: string;
  shardingKey?: (string | number | Date)[];
  superShardingKey?: (string | number | Date)[];
}

interface PoolAttributes extends ConnectionAttributes {
  poolAlias?: string;
  poolIncrement?: number;
  poolMax?: number;
  poolMaxPerShard?: number;
  poolMin?: number;
  poolPingInterval?: number;
  poolPingTimeout?: number;
  poolTimeout?: number;
  queueMax?: number;
  queueTimeout?: number;
  sessionCallback?: string | SessionCallback;
  sodaMetaDataCache?: boolean;
  enableStatistics?: boolean;
}

interface ExecuteOptions {
  autoCommit?: boolean;
  fetchArraySize?: number;
  fetchInfo?: {[key: string]: FetchInfo};
  fetchTypeHandler?: FetchTypeHandler;
  keepInStmtCache?: boolean;
  maxRows?: number;
  outFormat?: number;
  prefetchRows?: number;
  resultSet?: boolean;
}

type BindParameters = BindParametersObject | BindParametersArray;
type BindParametersObject = {[key: string]: BindValue};
type BindParametersArray = BindValue[];

interface BindValue {
  val?: any;
  dir?: number;
  type?: number;
  maxSize?: number;
  maxArraySize?: number;
}

interface StartupAttributes {
  force?: boolean;
  restrict?: boolean;
  pfile?: string;
}

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

interface ConfigProviderArgs {
  provider_arg?: string;
  urlExtendedPart?: string;
  credential?: any;
  paramMap?: Map<string, any>;
}

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

interface AccessToken {
  token: string;
  privateKey?: string;
}

interface ColumnMetaData {
  name: string;
  fetchType?: number;
  dbType: number;
  byteSize: number;
  precision?: number;
  scale?: number;
  nullable: boolean;
}

interface FetchInfo {
  type?: number;
  converter?: (value: any) => any;
}

interface SodaCollectionOptions {
  metaData?: any;
  mode?: number;
}

interface SodaDocumentOptions {
  key?: string;
  mediaType?: string;
}

interface GetCollectionNamesOptions {
  startsWith?: string;
  limit?: number;
}

type SodaDocumentArray = SodaDocument[];

Classes

Data Type Classes

// JSON ID class for SODA document keys
class JsonId extends Uint8Array {
  toJSON(): string;
}

// Sparse vector representation for vector data types
class SparseVector {
  constructor(input?: { indices: number[], values: number[], numDimensions: number });
  indices: Uint32Array;  
  values: Float64Array;
  numDimensions: number;
  toJSON(): any;
}

// Interval Year-to-Month representation
class IntervalYM {
  constructor(obj?: { years?: number, months?: number });
  years: number;
  months: number;
}

// Interval Day-to-Second representation  
class IntervalDS {
  constructor(obj?: { days?: number, hours?: number, minutes?: number, seconds?: number, fseconds?: number });
  days: number;
  hours: number;
  minutes: number;
  seconds: number;
  fseconds: number;
}

// Base database object class
class BaseDbObject {
  [key: string]: any;
}

Advanced Queuing Classes

// AQ Dequeue options configuration
class AqDeqOptions {
  condition?: string;
  consumerName?: string;
  correlation?: string;
  mode?: number;
  msgId?: Buffer;
  navigation?: number;
  transformation?: string;
  visibility?: number;
  wait?: number;
}

// AQ Enqueue options configuration
class AqEnqOptions {
  deliveryMode?: number;
  transformation?: string;
  visibility?: number;
}

// AQ Message representation
class AqMessage {
  correlation?: string;
  delay?: number;
  deliveryMode?: number;
  exceptionQueue?: string;
  expiration?: number;
  msgId?: Buffer;
  payload?: any;
  priority?: number;
  recipients?: string[];
}

// AQ Queue interface
class AqQueue {
  name: string;
  deqOptions: AqDeqOptions;
  enqOptions: AqEnqOptions;
  payloadType?: number;
  payloadTypeName?: string;
  
  deqOne(options?: AqDeqOptions): Promise<AqMessage>;
  deqMany(maxMessages: number, options?: AqDeqOptions): Promise<AqMessage[]>;
  enqOne(message: AqMessage, options?: AqEnqOptions): Promise<void>;
  enqMany(messages: AqMessage[], options?: AqEnqOptions): Promise<void>;
}

SODA Classes

// SODA Database interface
class SodaDatabase {
  createCollection(name: string, options?: SodaCollectionOptions): Promise<SodaCollection>;
  createDocument(content: any, options?: SodaDocumentOptions): SodaDocument;
  getCollectionNames(options?: GetCollectionNamesOptions): Promise<string[]>;
  openCollection(name: string): Promise<SodaCollection>;
}

// SODA Collection interface
class SodaCollection {
  metaData: any;
  name: string;
  
  createIndex(indexSpec: any): Promise<void>;
  drop(): Promise<{ dropped: boolean }>;
  dropIndex(indexName: string, options?: { force?: boolean }): Promise<void>;
  find(): SodaOperation;
  getDataGuide(): Promise<SodaDocument>;
  insertMany(documents: (SodaDocument | any)[]): Promise<SodaDocumentArray>;
  insertManyAndGet(documents: (SodaDocument | any)[]): Promise<SodaDocumentArray>;
  insertOne(document: SodaDocument | any): Promise<void>;
  insertOneAndGet(document: SodaDocument | any): Promise<SodaDocument>;
  save(document: SodaDocument): Promise<void>;
  saveAndGet(document: SodaDocument): Promise<SodaDocument>;
}

// SODA Document representation
class SodaDocument {
  createdOn?: string;
  key?: string;
  lastModified?: string;
  mediaType?: string;
  version?: string;
  
  getContent(): any;
  getContentAsBuffer(): Buffer;
  getContentAsString(): string;
}

// SODA Document cursor for iteration
class SodaDocCursor {
  getNext(): Promise<SodaDocument>;
  close(): Promise<void>;
}

// SODA Operation builder for find operations
class SodaOperation {
  count(): Promise<{ count: number }>;
  filter(filterSpec: any): SodaOperation;
  getCursor(): Promise<SodaDocCursor>;
  getDocuments(): Promise<SodaDocument[]>;
  getOne(): Promise<SodaDocument>;
  hint(hint: string): SodaOperation;
  key(key: string): SodaOperation;
  keys(keys: string[]): SodaOperation;
  limit(limit: number): SodaOperation; 
  remove(): Promise<{ count: number }>;
  replaceOne(document: SodaDocument | any): Promise<{ replaced: boolean }>;
  replaceOneAndGet(document: SodaDocument | any): Promise<SodaDocument>;
  skip(skip: number): SodaOperation;
  version(version: string): SodaOperation;
}

Pool Statistics Class

// Pool connection statistics
class PoolStatistics {
  connectionsInUse: number;
  connectionsOpen: number;
  poolAlias: string;
  poolMax: number;
  poolMaxPerShard: number;
  poolMin: number;
  queueLength: number;
  queueMax: number; 
  queueTimeout: number;
  requestsDequeued: number;
  requestsEnqueued: number;
  requestsNeverQueued: number;
  sessionTimeouts: number;
  shard: any[];
  stmtCacheHits: number;
  stmtCacheMisses: number;
}

Constants

// CQN Operation Codes
const CQN_OPCODE_ALL_OPS: 0;
const CQN_OPCODE_ALL_ROWS: 1;
const CQN_OPCODE_ALTER: 16;
const CQN_OPCODE_DELETE: 8;
const CQN_OPCODE_DROP: 32;
const CQN_OPCODE_INSERT: 2;
const CQN_OPCODE_UPDATE: 4;

// Statement Types
const STMT_TYPE_UNKNOWN: 0;
const STMT_TYPE_SELECT: 1;
const STMT_TYPE_UPDATE: 2;
const STMT_TYPE_DELETE: 3;
const STMT_TYPE_INSERT: 4;
const STMT_TYPE_CREATE: 5;
const STMT_TYPE_DROP: 6;
const STMT_TYPE_ALTER: 7;
const STMT_TYPE_BEGIN: 8;
const STMT_TYPE_DECLARE: 9;
const STMT_TYPE_CALL: 10;
const STMT_TYPE_EXPLAIN_PLAN: 15;
const STMT_TYPE_MERGE: 16;
const STMT_TYPE_ROLLBACK: 17;
const STMT_TYPE_COMMIT: 21;

// Shutdown Modes
const SHUTDOWN_MODE_DEFAULT: 0;
const SHUTDOWN_MODE_TRANSACTIONAL: 1;
const SHUTDOWN_MODE_TRANSACTIONAL_LOCAL: 2;
const SHUTDOWN_MODE_IMMEDIATE: 3;
const SHUTDOWN_MODE_ABORT: 4;
const SHUTDOWN_MODE_FINAL: 5;

// Startup Modes
const STARTUP_MODE_DEFAULT: 0;
const STARTUP_MODE_FORCE: 1;
const STARTUP_MODE_RESTRICT: 2;

// Subscription Event Types
const SUBSCR_EVENT_TYPE_SHUTDOWN: 2;
const SUBSCR_EVENT_TYPE_SHUTDOWN_ANY: 3;
const SUBSCR_EVENT_TYPE_STARTUP: 1;
const SUBSCR_EVENT_TYPE_DEREG: 5;
const SUBSCR_EVENT_TYPE_OBJ_CHANGE: 6;
const SUBSCR_EVENT_TYPE_QUERY_CHANGE: 7;
const SUBSCR_EVENT_TYPE_AQ: 100;

// Subscription Grouping Classes
const SUBSCR_GROUPING_CLASS_TIME: 1;

// Subscription Grouping Types  
const SUBSCR_GROUPING_TYPE_SUMMARY: 1;
const SUBSCR_GROUPING_TYPE_LAST: 2;

// Subscription Namespaces
const SUBSCR_NAMESPACE_AQ: 1;
const SUBSCR_NAMESPACE_DBCHANGE: 2;

// Subscription QoS Flags
const SUBSCR_QOS_BEST_EFFORT: 0x10;
const SUBSCR_QOS_DEREG_NFY: 0x02;
const SUBSCR_QOS_QUERY: 0x08;
const SUBSCR_QOS_RELIABLE: 0x01;
const SUBSCR_QOS_ROWIDS: 0x04;

// Privileges
const SYSASM: 0x00008000;
const SYSBACKUP: 0x00020000;
const SYSDBA: 0x00000002;
const SYSDG: 0x00040000;
const SYSKM: 0x00080000;
const SYSOPER: 0x00000004;
const SYSPRELIM: 0x00000008;
const SYSRAC: 0x00100000;

// Bind Directions
const BIND_IN: 3001;
const BIND_INOUT: 3002;
const BIND_OUT: 3003;

// Output Formats
const OUT_FORMAT_ARRAY: 4001;
const OUT_FORMAT_OBJECT: 4002;

// SODA Collection Creation Modes
const SODA_COLL_MAP_MODE: 5001;

// Pool Statuses
const POOL_STATUS_OPEN: 6000;
const POOL_STATUS_DRAINING: 6001;
const POOL_STATUS_CLOSED: 6002;
const POOL_STATUS_RECONFIGURING: 6003;

// AQ Dequeue Wait Options
const AQ_DEQ_NO_WAIT: 0;
const AQ_DEQ_WAIT_FOREVER: 4294967295;

// AQ Dequeue Modes
const AQ_DEQ_MODE_BROWSE: 1;
const AQ_DEQ_MODE_LOCKED: 2;
const AQ_DEQ_MODE_REMOVE: 3;
const AQ_DEQ_MODE_REMOVE_NO_DATA: 4;

// AQ Dequeue Navigation Flags
const AQ_DEQ_NAV_FIRST_MSG: 1;
const AQ_DEQ_NAV_NEXT_TRANSACTION: 2;
const AQ_DEQ_NAV_NEXT_MSG: 3;

// AQ Message Delivery Modes
const AQ_MSG_DELIV_MODE_PERSISTENT: 1;
const AQ_MSG_DELIV_MODE_BUFFERED: 2;
const AQ_MSG_DELIV_MODE_PERSISTENT_OR_BUFFERED: 3;

// AQ Message States
const AQ_MSG_STATE_READY: 0;
const AQ_MSG_STATE_WAITING: 1;
const AQ_MSG_STATE_PROCESSED: 2;
const AQ_MSG_STATE_EXPIRED: 3;

// AQ Visibility Flags
const AQ_VISIBILITY_IMMEDIATE: 1;
const AQ_VISIBILITY_ON_COMMIT: 2;

// TPC/XA Begin Flags
const TPC_BEGIN_JOIN: 0x00000002;
const TPC_BEGIN_NEW: 0x00000001;
const TPC_BEGIN_PROMOTE: 0x00000008;
const TPC_BEGIN_RESUME: 0x00000004;

// TPC/XA Two-Phase Commit Flags
const TPC_END_NORMAL: 0;
const TPC_END_SUSPEND: 0x00100000;

// Vector Formats
const VECTOR_FORMAT_FLOAT32: 2;
const VECTOR_FORMAT_FLOAT64: 3;
const VECTOR_FORMAT_INT8: 4;
const VECTOR_FORMAT_BINARY: 5;