0
# Configuration and Settings
1

2
Global configuration options and runtime settings for optimal performance and behavior customization.
3

4
## Capabilities
5

6
### Global Settings
7

8
Configure oracledb behavior globally across all connections and operations.
9

10
```javascript { .api }
11
// Connection and authentication settings
12
oracledb.autoCommit: boolean;                    // Auto-commit transactions
13
oracledb.connectionClass: string;                // Connection class identifier
14
oracledb.edition: string;                        // Database edition
15
oracledb.events: boolean;                        // Enable Oracle events
16
oracledb.externalAuth: boolean;                  // Use external authentication
17
oracledb.errorOnConcurrentExecute: boolean;      // Error on concurrent execute operations
18

19
// Connection identification settings
20
oracledb.driverName: string;                     // Driver name identification
21
oracledb.machine: string;                        // Machine name for connection
22
oracledb.osUser: string;                         // Operating system user name
23
oracledb.program: string;                        // Program name identification  
24
oracledb.terminal: string;                       // Terminal identifier
25

26
// Data handling settings
27
oracledb.dbObjectAsPojo: boolean;               // Return DB objects as plain objects
28
oracledb.fetchArraySize: number;                // Fetch array size for performance
29
oracledb.fetchAsBuffer: number[];               // Types to fetch as Buffer
30
oracledb.fetchAsString: number[];               // Types to fetch as String
31
oracledb.fetchTypeHandler: FetchTypeHandler;    // Custom fetch type handler
32
oracledb.dbObjectTypeHandler: DbObjectTypeHandler; // Custom DB object type handler
33

34
// Result formatting
35
oracledb.maxRows: number;                       // Maximum rows to fetch
36
oracledb.outFormat: number;                     // Output format (ARRAY or OBJECT)
37
oracledb.prefetchRows: number;                  // Prefetch rows for performance
38

39
// LOB handling
40
oracledb.lobPrefetchSize: number;              // LOB prefetch size
41

42
// Connection pooling
43
oracledb.poolIncrement: number;                 // Pool increment size
44
oracledb.poolMax: number;                       // Maximum pool connections
45
oracledb.poolMaxPerShard: number;              // Maximum connections per shard
46
oracledb.poolMin: number;                       // Minimum pool connections
47
oracledb.poolPingInterval: number;              // Pool ping interval (seconds)
48
oracledb.poolPingTimeout: number;               // Pool ping timeout (seconds)
49
oracledb.poolTimeout: number;                   // Pool connection timeout (seconds)
50

51
// Statement handling
52
oracledb.stmtCacheSize: number;                // Statement cache size
53

54
// Configuration providers
55
oracledb.configProviderCacheTimeout: number;   // Config provider cache timeout
56

57
// Version information (read-only)
58
oracledb.version: number;                      // Driver version number
59
oracledb.versionString: string;                // Driver version string
60
oracledb.versionSuffix: string;                // Driver version suffix
61
oracledb.oracleClientVersion: number;          // Oracle Client version
62
oracledb.oracleClientVersionString: string;    // Oracle Client version string
63
oracledb.thin: boolean;                        // Thin mode enabled
64
```
65

66
**Usage Examples:**
67

68
```javascript
69
const oracledb = require('oracledb');
70

71
// Basic configuration
72
oracledb.autoCommit = true;
73
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
74
oracledb.fetchArraySize = 100;
75

76
// Connection settings
77
oracledb.connectionClass = 'MYAPP';
78
oracledb.events = true;
79
oracledb.errorOnConcurrentExecute = true;
80

81
// Connection identification
82
oracledb.driverName = 'MyApp v1.0 : node-oracledb';
83
oracledb.machine = 'webapp-server-01';
84
oracledb.osUser = 'webapp';
85
oracledb.program = 'MyWebApp';
86
oracledb.terminal = 'webapp-term';
87

88
// Pool configuration
89
oracledb.poolMin = 5;
90
oracledb.poolMax = 20;
91
oracledb.poolIncrement = 2;
92
oracledb.poolTimeout = 300;
93

94
// Performance tuning
95
oracledb.prefetchRows = 100;
96
oracledb.stmtCacheSize = 50;
97

98
console.log('Driver version:', oracledb.versionString);
99
console.log('Oracle Client version:', oracledb.oracleClientVersionString);
100
console.log('Thin mode:', oracledb.thin);
101
```
102

103
### Data Type Conversion
104

105
Configure how Oracle data types are converted to JavaScript types.
106

107
```javascript { .api }
108
// Fetch as different types
109
oracledb.fetchAsString: number[];  // Array of Oracle type constants
110
oracledb.fetchAsBuffer: number[];  // Array of Oracle type constants
111

112
// Custom type handlers
113
type FetchTypeHandler = (metadata: Metadata) => FetchTypeResult;
114

115
interface FetchTypeResult {
116
  type?: number;
117
  converter?: (value: any) => any;
118
}
119

120
type DbObjectTypeHandler = (metadata: Metadata) => DbObjectTypeResult;
121

122
interface DbObjectTypeResult {
123
  converter?: (value: any) => any;
124
}
125
```
126

127
**Usage Examples:**
128

129
```javascript
130
// Fetch NUMBERs and DATEs as strings for precision
131
oracledb.fetchAsString = [oracledb.NUMBER, oracledb.DATE];
132

133
// Fetch CLOBs as strings instead of Lob objects
134
oracledb.fetchAsString = [oracledb.CLOB];
135

136
// Custom fetch type handler
137
oracledb.fetchTypeHandler = (metadata) => {
138
  // Convert all NUMBERs with scale > 0 to strings for precision
139
  if (metadata.dbType === oracledb.NUMBER && metadata.scale > 0) {
140
    return { type: oracledb.STRING };
141
  }
142
  
143
  // Convert DATEs to ISO strings
144
  if (metadata.dbType === oracledb.DATE) {
145
    return {
146
      converter: (val) => val ? val.toISOString() : null
147
    };
148
  }
149
  
150
  // Convert CLOBs directly to strings
151
  if (metadata.dbType === oracledb.CLOB) {
152
    return {
153
      type: oracledb.STRING,
154
      converter: async (lob) => {
155
        if (lob) {
156
          const data = await lob.getData();
157
          await lob.close();
158
          return data;
159
        }
160
        return null;
161
      }
162
    };
163
  }
164
};
165

166
// Custom DB object type handler
167
oracledb.dbObjectTypeHandler = (metadata) => {
168
  return {
169
    converter: (obj) => {
170
      // Convert Oracle object property names to camelCase
171
      if (obj && typeof obj === 'object') {
172
        const result = {};
173
        for (const [key, value] of Object.entries(obj)) {
174
          const camelKey = key.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
175
          result[camelKey] = value;
176
        }
177
        return result;
178
      }
179
      return obj;
180
    }
181
  };
182
};
183
```
184

185
### Performance Tuning
186

187
Optimize performance through configuration settings.
188

189
```javascript { .api }
190
// Array fetch size for bulk operations
191
oracledb.fetchArraySize: number;    // Default: 100
192

193
// Prefetch rows for SELECT statements
194
oracledb.prefetchRows: number;      // Default: 2
195

196
// LOB prefetch size
197
oracledb.lobPrefetchSize: number;   // Default: 16384
198

199
// Statement cache size
200
oracledb.stmtCacheSize: number;     // Default: 30
201

202
// Maximum rows to fetch
203
oracledb.maxRows: number;           // Default: 0 (unlimited)
204
```
205

206
**Usage Examples:**
207

208
```javascript
209
// High-performance bulk operations
210
oracledb.fetchArraySize = 1000;     // Fetch 1000 rows at a time
211
oracledb.prefetchRows = 1000;       // Prefetch 1000 rows
212

213
// Memory-conscious settings for large datasets
214
oracledb.maxRows = 10000;           // Limit result sets
215
oracledb.fetchArraySize = 100;      // Smaller fetch size
216

217
// Optimize for CLOB/BLOB operations
218
oracledb.lobPrefetchSize = 65536;   // 64KB prefetch for LOBs
219

220
// Statement caching for repeated operations
221
oracledb.stmtCacheSize = 100;       // Cache 100 prepared statements
222

223
// Measure performance impact
224
console.time('query');
225
const result = await connection.execute('SELECT * FROM large_table');
226
console.timeEnd('query');
227
console.log('Rows fetched:', result.rows.length);
228
```
229

230
### Output Formatting
231

232
Configure how query results are formatted and returned.
233

234
```javascript { .api }
235
// Output format constants
236
const OUT_FORMAT_ARRAY = 4001;     // Results as arrays
237
const OUT_FORMAT_OBJECT = 4002;    // Results as objects
238

239
// Aliases
240
const ARRAY = OUT_FORMAT_ARRAY;
241
const OBJECT = OUT_FORMAT_OBJECT;
242

243
// Global setting
244
oracledb.outFormat: number;
245

246
// Per-query setting
247
interface ExecuteOptions {
248
  outFormat?: number;
249
}
250
```
251

252
**Usage Examples:**
253

254
```javascript
255
// Global output format
256
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
257

258
const result = await connection.execute(
259
  'SELECT employee_id, first_name, last_name FROM employees WHERE rownum <= 3'
260
);
261

262
console.log(result.rows);
263
// Output:
264
// [
265
//   { EMPLOYEE_ID: 100, FIRST_NAME: 'Steven', LAST_NAME: 'King' },
266
//   { EMPLOYEE_ID: 101, FIRST_NAME: 'Neena', LAST_NAME: 'Kochhar' },
267
//   { EMPLOYEE_ID: 102, FIRST_NAME: 'Lex', LAST_NAME: 'De Haan' }
268
// ]
269

270
// Override per query
271
const arrayResult = await connection.execute(
272
  'SELECT employee_id, first_name FROM employees WHERE rownum <= 2',
273
  [],
274
  { outFormat: oracledb.OUT_FORMAT_ARRAY }
275
);
276

277
console.log(arrayResult.rows);
278
// Output: [[100, 'Steven'], [101, 'Neena']]
279
```
280

281
### Oracle Client Configuration
282

283
Configure Oracle Client libraries and initialization.
284

285
```javascript { .api }
286
/**
287
 * Initializes Oracle Client libraries (thick mode)
288
 * @param options - Client initialization options
289
 */
290
function initOracleClient(options?: InitOracleClientOptions): void;
291

292
interface InitOracleClientOptions {
293
  libDir?: string;        // Oracle Client library directory
294
  configDir?: string;     // Configuration files directory
295
  errorUrl?: string;      // Custom error URL
296
  driverName?: string;    // Custom driver name
297
}
298
```
299

300
**Usage Examples:**
301

302
```javascript
303
// Initialize Oracle Client for thick mode
304
oracledb.initOracleClient({
305
  libDir: '/usr/lib/oracle/19.3/client64/lib',
306
  configDir: '/usr/lib/oracle/19.3/client64/lib/network/admin',
307
  driverName: 'MyApp : 1.0'
308
});
309

310
// Check which mode is active
311
console.log('Thin mode active:', oracledb.thin);
312

313
// Get Oracle Client version (thick mode only)
314
if (!oracledb.thin) {
315
  console.log('Oracle Client version:', oracledb.oracleClientVersionString);
316
}
317
```
318

319
### Configuration Providers
320

321
Register configuration providers for dynamic settings.
322

323
```javascript { .api }
324
/**
325
 * Registers a configuration provider hook
326
 * @param hookFn - Configuration provider function
327
 */
328
function registerConfigurationProviderHook(hookFn: ConfigurationProviderHook): void;
329

330
/**
331
 * Registers a process configuration hook
332
 * @param hookFn - Process configuration function
333
 */
334
function registerProcessConfigurationHook(hookFn: ProcessConfigurationHook): void;
335

336
type ConfigurationProviderHook = (options: any) => Promise<any>;
337
type ProcessConfigurationHook = (options: any) => Promise<any>;
338

339
// Configuration provider cache timeout
340
oracledb.configProviderCacheTimeout: number;  // Default: 300 seconds
341
```
342

343
**Usage Examples:**
344

345
```javascript
346
// Register a configuration provider
347
oracledb.registerConfigurationProviderHook(async (options) => {
348
  // Fetch configuration from external source
349
  const response = await fetch('https://config-service.example.com/db-config');
350
  const config = await response.json();
351
  
352
  return {
353
    user: config.username,
354
    password: config.password,
355
    connectString: config.connectionString
356
  };
357
});
358

359
// Register a process configuration hook
360
oracledb.registerProcessConfigurationHook(async (options) => {
361
  // Set up process-level configuration
362
  process.env.TNS_ADMIN = '/opt/oracle/network/admin';
363
  process.env.NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8';
364
  
365
  return options;
366
});
367

368
// Configuration will be cached for the specified timeout
369
oracledb.configProviderCacheTimeout = 600; // 10 minutes
370
```
371

372
### Runtime Information
373

374
Access runtime version and capability information.
375

376
```javascript { .api }
377
// Version information (read-only)
378
oracledb.version: number;                      // e.g., 60900
379
oracledb.versionString: string;                // e.g., "6.9.0"
380
oracledb.versionSuffix: string;                // e.g., "-dev"
381

382
// Oracle Client information (thick mode only)
383
oracledb.oracleClientVersion: number;          // Oracle Client version
384
oracledb.oracleClientVersionString: string;    // Oracle Client version string
385

386
// Mode information
387
oracledb.thin: boolean;                        // true if thin mode active
388
```
389

390
**Usage Examples:**
391

392
```javascript
393
// Display version information
394
console.log('node-oracledb version:', oracledb.versionString);
395
console.log('Version number:', oracledb.version);
396
console.log('Version suffix:', oracledb.versionSuffix);
397

398
// Check capabilities based on mode
399
if (oracledb.thin) {
400
  console.log('Running in thin mode - no Oracle Client required');
401
} else {
402
  console.log('Running in thick mode with Oracle Client:', oracledb.oracleClientVersionString);
403
}
404

405
// Version-dependent feature checks
406
if (oracledb.version >= 60000) {
407
  console.log('SODA operations supported');
408
}
409

410
if (oracledb.version >= 60900) {
411
  console.log('Sessionless transactions supported');
412
}
413
```
414

415
### Error Handling Configuration
416

417
Configure error handling behavior and URLs.
418

419
```javascript { .api }
420
interface InitOracleClientOptions {
421
  errorUrl?: string;      // Custom error documentation URL
422
  driverName?: string;    // Custom driver name for identification
423
}
424
```
425

426
**Usage Examples:**
427

428
```javascript
429
// Custom error handling
430
oracledb.initOracleClient({
431
  errorUrl: 'https://mydocs.example.com/oracle-errors',
432
  driverName: 'MyApplication v2.1 : node-oracledb'
433
});
434

435
// Errors will reference the custom URL and driver name
436
try {
437
  await connection.execute('INVALID SQL');
438
} catch (error) {
439
  console.log('Error message includes custom context');
440
  console.log(error.message);
441
}
442
```