Configuration and Settings

// Connection and authentication settings
oracledb.autoCommit: boolean;                    // Auto-commit transactions
oracledb.connectionClass: string;                // Connection class identifier
oracledb.edition: string;                        // Database edition
oracledb.events: boolean;                        // Enable Oracle events
oracledb.externalAuth: boolean;                  // Use external authentication
oracledb.errorOnConcurrentExecute: boolean;      // Error on concurrent execute operations

// Connection identification settings
oracledb.driverName: string;                     // Driver name identification
oracledb.machine: string;                        // Machine name for connection
oracledb.osUser: string;                         // Operating system user name
oracledb.program: string;                        // Program name identification  
oracledb.terminal: string;                       // Terminal identifier

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

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

// LOB handling
oracledb.lobPrefetchSize: number;              // LOB prefetch size

// Connection pooling
oracledb.poolIncrement: number;                 // Pool increment size
oracledb.poolMax: number;                       // Maximum pool connections
oracledb.poolMaxPerShard: number;              // Maximum connections per shard
oracledb.poolMin: number;                       // Minimum pool connections
oracledb.poolPingInterval: number;              // Pool ping interval (seconds)
oracledb.poolPingTimeout: number;               // Pool ping timeout (seconds)
oracledb.poolTimeout: number;                   // Pool connection timeout (seconds)

// Statement handling
oracledb.stmtCacheSize: number;                // Statement cache size

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

// Version information (read-only)
oracledb.version: number;                      // Driver version number
oracledb.versionString: string;                // Driver version string
oracledb.versionSuffix: string;                // Driver version suffix
oracledb.oracleClientVersion: number;          // Oracle Client version
oracledb.oracleClientVersionString: string;    // Oracle Client version string
oracledb.thin: boolean;                        // Thin mode enabled

const oracledb = require('oracledb');

// Basic configuration
oracledb.autoCommit = true;
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
oracledb.fetchArraySize = 100;

// Connection settings
oracledb.connectionClass = 'MYAPP';
oracledb.events = true;
oracledb.errorOnConcurrentExecute = true;

// Connection identification
oracledb.driverName = 'MyApp v1.0 : node-oracledb';
oracledb.machine = 'webapp-server-01';
oracledb.osUser = 'webapp';
oracledb.program = 'MyWebApp';
oracledb.terminal = 'webapp-term';

// Pool configuration
oracledb.poolMin = 5;
oracledb.poolMax = 20;
oracledb.poolIncrement = 2;
oracledb.poolTimeout = 300;

// Performance tuning
oracledb.prefetchRows = 100;
oracledb.stmtCacheSize = 50;

console.log('Driver version:', oracledb.versionString);
console.log('Oracle Client version:', oracledb.oracleClientVersionString);
console.log('Thin mode:', oracledb.thin);

// Fetch as different types
oracledb.fetchAsString: number[];  // Array of Oracle type constants
oracledb.fetchAsBuffer: number[];  // Array of Oracle type constants

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

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

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

interface DbObjectTypeResult {
  converter?: (value: any) => any;
}

// Fetch NUMBERs and DATEs as strings for precision
oracledb.fetchAsString = [oracledb.NUMBER, oracledb.DATE];

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

// Custom fetch type handler
oracledb.fetchTypeHandler = (metadata) => {
  // Convert all NUMBERs with scale > 0 to strings for precision
  if (metadata.dbType === oracledb.NUMBER && metadata.scale > 0) {
    return { type: oracledb.STRING };
  }
  
  // Convert DATEs to ISO strings
  if (metadata.dbType === oracledb.DATE) {
    return {
      converter: (val) => val ? val.toISOString() : null
    };
  }
  
  // Convert CLOBs directly to strings
  if (metadata.dbType === oracledb.CLOB) {
    return {
      type: oracledb.STRING,
      converter: async (lob) => {
        if (lob) {
          const data = await lob.getData();
          await lob.close();
          return data;
        }
        return null;
      }
    };
  }
};

// Custom DB object type handler
oracledb.dbObjectTypeHandler = (metadata) => {
  return {
    converter: (obj) => {
      // Convert Oracle object property names to camelCase
      if (obj && typeof obj === 'object') {
        const result = {};
        for (const [key, value] of Object.entries(obj)) {
          const camelKey = key.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
          result[camelKey] = value;
        }
        return result;
      }
      return obj;
    }
  };
};

// Array fetch size for bulk operations
oracledb.fetchArraySize: number;    // Default: 100

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

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

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

// Maximum rows to fetch
oracledb.maxRows: number;           // Default: 0 (unlimited)

// High-performance bulk operations
oracledb.fetchArraySize = 1000;     // Fetch 1000 rows at a time
oracledb.prefetchRows = 1000;       // Prefetch 1000 rows

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

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

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

// Measure performance impact
console.time('query');
const result = await connection.execute('SELECT * FROM large_table');
console.timeEnd('query');
console.log('Rows fetched:', result.rows.length);

// Output format constants
const OUT_FORMAT_ARRAY = 4001;     // Results as arrays
const OUT_FORMAT_OBJECT = 4002;    // Results as objects

// Aliases
const ARRAY = OUT_FORMAT_ARRAY;
const OBJECT = OUT_FORMAT_OBJECT;

// Global setting
oracledb.outFormat: number;

// Per-query setting
interface ExecuteOptions {
  outFormat?: number;
}

// Global output format
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;

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

console.log(result.rows);
// Output:
// [
//   { EMPLOYEE_ID: 100, FIRST_NAME: 'Steven', LAST_NAME: 'King' },
//   { EMPLOYEE_ID: 101, FIRST_NAME: 'Neena', LAST_NAME: 'Kochhar' },
//   { EMPLOYEE_ID: 102, FIRST_NAME: 'Lex', LAST_NAME: 'De Haan' }
// ]

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

console.log(arrayResult.rows);
// Output: [[100, 'Steven'], [101, 'Neena']]

/**
 * Initializes Oracle Client libraries (thick mode)
 * @param options - Client initialization options
 */
function initOracleClient(options?: InitOracleClientOptions): void;

interface InitOracleClientOptions {
  libDir?: string;        // Oracle Client library directory
  configDir?: string;     // Configuration files directory
  errorUrl?: string;      // Custom error URL
  driverName?: string;    // Custom driver name
}

// Initialize Oracle Client for thick mode
oracledb.initOracleClient({
  libDir: '/usr/lib/oracle/19.3/client64/lib',
  configDir: '/usr/lib/oracle/19.3/client64/lib/network/admin',
  driverName: 'MyApp : 1.0'
});

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

// Get Oracle Client version (thick mode only)
if (!oracledb.thin) {
  console.log('Oracle Client version:', oracledb.oracleClientVersionString);
}

/**
 * Registers a configuration provider hook
 * @param hookFn - Configuration provider function
 */
function registerConfigurationProviderHook(hookFn: ConfigurationProviderHook): void;

/**
 * Registers a process configuration hook
 * @param hookFn - Process configuration function
 */
function registerProcessConfigurationHook(hookFn: ProcessConfigurationHook): void;

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

// Configuration provider cache timeout
oracledb.configProviderCacheTimeout: number;  // Default: 300 seconds

// Register a configuration provider
oracledb.registerConfigurationProviderHook(async (options) => {
  // Fetch configuration from external source
  const response = await fetch('https://config-service.example.com/db-config');
  const config = await response.json();
  
  return {
    user: config.username,
    password: config.password,
    connectString: config.connectionString
  };
});

// Register a process configuration hook
oracledb.registerProcessConfigurationHook(async (options) => {
  // Set up process-level configuration
  process.env.TNS_ADMIN = '/opt/oracle/network/admin';
  process.env.NLS_LANG = 'AMERICAN_AMERICA.AL32UTF8';
  
  return options;
});

// Configuration will be cached for the specified timeout
oracledb.configProviderCacheTimeout = 600; // 10 minutes

// Version information (read-only)
oracledb.version: number;                      // e.g., 60900
oracledb.versionString: string;                // e.g., "6.9.0"
oracledb.versionSuffix: string;                // e.g., "-dev"

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

// Mode information
oracledb.thin: boolean;                        // true if thin mode active

// Display version information
console.log('node-oracledb version:', oracledb.versionString);
console.log('Version number:', oracledb.version);
console.log('Version suffix:', oracledb.versionSuffix);

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

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

if (oracledb.version >= 60900) {
  console.log('Sessionless transactions supported');
}

interface InitOracleClientOptions {
  errorUrl?: string;      // Custom error documentation URL
  driverName?: string;    // Custom driver name for identification
}

// Custom error handling
oracledb.initOracleClient({
  errorUrl: 'https://mydocs.example.com/oracle-errors',
  driverName: 'MyApplication v2.1 : node-oracledb'
});

// Errors will reference the custom URL and driver name
try {
  await connection.execute('INVALID SQL');
} catch (error) {
  console.log('Error message includes custom context');
  console.log(error.message);
}