Data Types and LOBs

interface Lob {
  // Read/write operations
  close(): Promise<void>;
  getData(): Promise<string | Buffer>;
  read(size?: number): Promise<string | Buffer>;
  write(data: string | Buffer): Promise<void>;
  
  // Properties
  chunkSize: number;    // Optimal chunk size for I/O (read-only)
  length: number;       // LOB length in bytes/characters (read-only)
  pieceSize: number;    // Size for piecewise operations
  type: number;         // LOB type (read-only)
}

/**
 * Creates a temporary LOB
 * @param type - LOB type (CLOB or BLOB)
 * @returns Promise resolving to Lob instance
 */
createLob(type: number): Promise<Lob>;

// LOB type constants
const DB_TYPE_CLOB = 112;    // Character LOB
const DB_TYPE_BLOB = 113;    // Binary LOB
const DB_TYPE_BFILE = 114;   // Binary file LOB
const DB_TYPE_NCLOB = 111;   // National Character LOB

const oracledb = require('oracledb');

// Reading a CLOB
const result = await connection.execute(
  'SELECT document_text FROM documents WHERE id = :1',
  [123]
);

const clob = result.rows[0][0];
if (clob) {
  const text = await clob.getData();
  console.log('Document text:', text);
  await clob.close();
}

// Writing to a CLOB
const tempClob = await connection.createLob(oracledb.CLOB);
await tempClob.write('Large amount of text data...');

await connection.execute(
  'UPDATE documents SET document_text = :clob WHERE id = :id',
  { clob: tempClob, id: 123 }
);

await tempClob.close();

// Streaming large BLOB data
const result = await connection.execute(
  'SELECT image_data FROM images WHERE id = :1',
  [456]
);

const blob = result.rows[0][0];
if (blob) {
  let chunk;
  const chunks = [];
  
  while ((chunk = await blob.read(64000))) {
    chunks.push(chunk);
  }
  
  const imageData = Buffer.concat(chunks);
  await blob.close();
}

/**
 * Gets a database object class constructor
 * @param name - Object type name
 * @returns Promise resolving to DbObject constructor
 */
getDbObjectClass(name: string): Promise<DbObjectClass>;

interface DbObjectClass {
  new(initialValue?: any): DbObject;
  prototype: DbObject;
}

interface DbObject {
  // Object inspection
  isCollection: boolean;
  
  // Collection operations (if isCollection is true)
  append(value: any): void;
  deleteElement(index: number): void;
  getElement(index: number): any;
  getFirstIndex(): number;
  getLastIndex(): number;
  getNext(index: number): number;
  getPrev(index: number): number;
  hasElement(index: number): boolean;
  setElement(index: number, value: any): void;
  size: number;
  
  // Conversion
  toJSON(): any;
}

class BaseDbObject {
  constructor(objType?: any);
  isCollection: boolean;
  toJSON(): any;
}

// Working with Oracle object types
// First create object type in database:
// CREATE TYPE person_t AS OBJECT (name VARCHAR2(50), age NUMBER);

const PersonClass = await connection.getDbObjectClass('PERSON_T');

// Create new object instance
const person = new PersonClass({
  name: 'John Doe',
  age: 30
});

// Use in SQL
await connection.execute(
  'INSERT INTO people VALUES (:person)',
  { person: person }
);

// Working with collections
// CREATE TYPE number_list AS TABLE OF NUMBER;

const NumberListClass = await connection.getDbObjectClass('NUMBER_LIST');
const numberList = new NumberListClass([1, 2, 3, 4, 5]);

// Collection operations
numberList.append(6);
console.log('Size:', numberList.size);
console.log('First element:', numberList.getElement(0));

// Use collection in SQL
const result = await connection.execute(
  'SELECT * FROM TABLE(:numbers)',
  { numbers: numberList }
);

// JSON type constants
const DB_TYPE_JSON = 119;

// JSON handling in queries and DML
interface JsonValue {
  toJSON(): any;
  toString(): string;
}

// Insert JSON data
const jsonData = {
  name: 'Product A',
  price: 99.99,
  features: ['feature1', 'feature2'],
  metadata: {
    category: 'electronics',
    inStock: true
  }
};

await connection.execute(
  'INSERT INTO products (id, data) VALUES (:1, :2)',
  [1, jsonData]
);

// Query JSON data
const result = await connection.execute(
  'SELECT data FROM products WHERE id = :1',
  [1]
);

const productData = result.rows[0][0];
console.log('Product name:', productData.name);
console.log('Features:', productData.features);

// JSON queries with SQL/JSON functions
const result2 = await connection.execute(`
  SELECT JSON_VALUE(data, '$.name') as product_name,
         JSON_VALUE(data, '$.price') as price
  FROM products 
  WHERE JSON_EXISTS(data, '$.metadata.inStock' returning true)
`);

// Fetch type handler for custom conversions
type FetchTypeHandler = (metadata: Metadata) => FetchTypeResult;

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

// DB Object type handler for custom object processing
type DbObjectTypeHandler = (metadata: Metadata) => DbObjectTypeResult;

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

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

// Custom fetch type handler
const customFetchTypeHandler = (metadata) => {
  // Convert NUMBER columns to strings for precision
  if (metadata.dbType === oracledb.DB_TYPE_NUMBER && metadata.scale > 0) {
    return {
      type: oracledb.STRING,
      converter: (val) => val ? parseFloat(val) : null
    };
  }
  
  // Convert DATE columns to custom format
  if (metadata.dbType === oracledb.DB_TYPE_DATE) {
    return {
      converter: (val) => val ? val.toISOString() : null
    };
  }
};

// Apply globally
oracledb.fetchTypeHandler = customFetchTypeHandler;

// Or per query
const result = await connection.execute(
  'SELECT price, created_date FROM products',
  [],
  { fetchTypeHandler: customFetchTypeHandler }
);

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

oracledb.dbObjectTypeHandler = customDbObjectTypeHandler;

// Interval types
class IntervalYM {
  constructor(years: number, months: number);
  years: number;
  months: number;
  toString(): string;
}

class IntervalDS {
  constructor(days: number, hours: number, minutes: number, seconds: number, fseconds: number);
  days: number;
  hours: number;
  minutes: number;
  seconds: number;
  fseconds: number;
  toString(): string;
}

// Vector types (Oracle 23c+)
class SparseVector {
  constructor(dimensions: number, values: {[index: number]: number});
  dimensions: number;
  values: {[index: number]: number};
}

// Vector format constants
const VECTOR_FORMAT_FLOAT32 = 2;
const VECTOR_FORMAT_FLOAT64 = 3;
const VECTOR_FORMAT_INT8 = 4;
const VECTOR_FORMAT_BINARY = 5;

// Working with interval types
const yearMonthInterval = new oracledb.IntervalYM(2, 6); // 2 years, 6 months
const daySecondInterval = new oracledb.IntervalDS(10, 5, 30, 45, 500000); // 10 days, 5:30:45.5

await connection.execute(
  'INSERT INTO events (name, duration_ym, duration_ds) VALUES (:1, :2, :3)',
  ['Annual Event', yearMonthInterval, daySecondInterval]
);

// Working with vectors (Oracle 23c+)
const vector = new oracledb.SparseVector(1000, {
  0: 0.5,
  10: 0.8,
  50: -0.3,
  999: 0.1
});

await connection.execute(
  'INSERT INTO embeddings (doc_id, vector_data) VALUES (:1, :2)',
  [123, vector]
);

// Vector similarity search
const searchVector = new oracledb.SparseVector(1000, {0: 0.6, 10: 0.7});
const result = await connection.execute(`
  SELECT doc_id, VECTOR_DISTANCE(vector_data, :search_vec) as similarity
  FROM embeddings
  ORDER BY similarity
  FETCH FIRST 10 ROWS ONLY
`, { search_vec: searchVector });

// Oracle database types
const DB_TYPE_BFILE = 114;
const DB_TYPE_BINARY_DOUBLE = 101;
const DB_TYPE_BINARY_FLOAT = 100;
const DB_TYPE_BINARY_INTEGER = 3;
const DB_TYPE_BLOB = 113;
const DB_TYPE_BOOLEAN = 252;
const DB_TYPE_CHAR = 96;
const DB_TYPE_CLOB = 112;
const DB_TYPE_CURSOR = 102;
const DB_TYPE_DATE = 12;
const DB_TYPE_INTERVAL_DS = 183;
const DB_TYPE_INTERVAL_YM = 182;
const DB_TYPE_JSON = 119;
const DB_TYPE_LONG = 8;
const DB_TYPE_LONG_NVARCHAR = 16;
const DB_TYPE_LONG_RAW = 24;
const DB_TYPE_NCHAR = 106;
const DB_TYPE_NCLOB = 111;
const DB_TYPE_NUMBER = 2;
const DB_TYPE_NVARCHAR = 1;
const DB_TYPE_OBJECT = 108;
const DB_TYPE_RAW = 23;
const DB_TYPE_ROWID = 104;
const DB_TYPE_TIMESTAMP = 187;
const DB_TYPE_TIMESTAMP_LTZ = 232;
const DB_TYPE_TIMESTAMP_TZ = 188;
const DB_TYPE_VARCHAR = 1;
const DB_TYPE_XMLTYPE = 109;
const DB_TYPE_VECTOR = 126;

// Type aliases for convenience
const BLOB = DB_TYPE_BLOB;
const BUFFER = DB_TYPE_RAW;
const CLOB = DB_TYPE_CLOB;
const CURSOR = DB_TYPE_CURSOR;
const DATE = DB_TYPE_TIMESTAMP;
const NCLOB = DB_TYPE_NCLOB;
const NUMBER = DB_TYPE_NUMBER;
const STRING = DB_TYPE_VARCHAR;