0
# Data Types and LOBs
1

2
Oracle-specific data types including Large Objects (LOBs), JSON, custom database objects, and advanced data handling capabilities.
3

4
## Capabilities
5

6
### Large Objects (LOBs)
7

8
Handle Large Objects for storing and retrieving large amounts of text or binary data.
9

10
```javascript { .api }
11
interface Lob {
12
  // Read/write operations
13
  close(): Promise<void>;
14
  getData(): Promise<string | Buffer>;
15
  read(size?: number): Promise<string | Buffer>;
16
  write(data: string | Buffer): Promise<void>;
17
  
18
  // Properties
19
  chunkSize: number;    // Optimal chunk size for I/O (read-only)
20
  length: number;       // LOB length in bytes/characters (read-only)
21
  pieceSize: number;    // Size for piecewise operations
22
  type: number;         // LOB type (read-only)
23
}
24

25
/**
26
 * Creates a temporary LOB
27
 * @param type - LOB type (CLOB or BLOB)
28
 * @returns Promise resolving to Lob instance
29
 */
30
createLob(type: number): Promise<Lob>;
31

32
// LOB type constants
33
const DB_TYPE_CLOB = 112;    // Character LOB
34
const DB_TYPE_BLOB = 113;    // Binary LOB
35
const DB_TYPE_BFILE = 114;   // Binary file LOB
36
const DB_TYPE_NCLOB = 111;   // National Character LOB
37
```
38

39
**Usage Examples:**
40

41
```javascript
42
const oracledb = require('oracledb');
43

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

50
const clob = result.rows[0][0];
51
if (clob) {
52
  const text = await clob.getData();
53
  console.log('Document text:', text);
54
  await clob.close();
55
}
56

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

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

66
await tempClob.close();
67

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

74
const blob = result.rows[0][0];
75
if (blob) {
76
  let chunk;
77
  const chunks = [];
78
  
79
  while ((chunk = await blob.read(64000))) {
80
    chunks.push(chunk);
81
  }
82
  
83
  const imageData = Buffer.concat(chunks);
84
  await blob.close();
85
}
86
```
87

88
### Database Objects
89

90
Work with Oracle object types and collections through database objects.
91

92
```javascript { .api }
93
/**
94
 * Gets a database object class constructor
95
 * @param name - Object type name
96
 * @returns Promise resolving to DbObject constructor
97
 */
98
getDbObjectClass(name: string): Promise<DbObjectClass>;
99

100
interface DbObjectClass {
101
  new(initialValue?: any): DbObject;
102
  prototype: DbObject;
103
}
104

105
interface DbObject {
106
  // Object inspection
107
  isCollection: boolean;
108
  
109
  // Collection operations (if isCollection is true)
110
  append(value: any): void;
111
  deleteElement(index: number): void;
112
  getElement(index: number): any;
113
  getFirstIndex(): number;
114
  getLastIndex(): number;
115
  getNext(index: number): number;
116
  getPrev(index: number): number;
117
  hasElement(index: number): boolean;
118
  setElement(index: number, value: any): void;
119
  size: number;
120
  
121
  // Conversion
122
  toJSON(): any;
123
}
124

125
class BaseDbObject {
126
  constructor(objType?: any);
127
  isCollection: boolean;
128
  toJSON(): any;
129
}
130
```
131

132
**Usage Examples:**
133

134
```javascript
135
// Working with Oracle object types
136
// First create object type in database:
137
// CREATE TYPE person_t AS OBJECT (name VARCHAR2(50), age NUMBER);
138

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

141
// Create new object instance
142
const person = new PersonClass({
143
  name: 'John Doe',
144
  age: 30
145
});
146

147
// Use in SQL
148
await connection.execute(
149
  'INSERT INTO people VALUES (:person)',
150
  { person: person }
151
);
152

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

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

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

164
// Use collection in SQL
165
const result = await connection.execute(
166
  'SELECT * FROM TABLE(:numbers)',
167
  { numbers: numberList }
168
);
169
```
170

171
### JSON Data Type
172

173
Handle Oracle's native JSON data type with full JSON functionality.
174

175
```javascript { .api }
176
// JSON type constants
177
const DB_TYPE_JSON = 119;
178

179
// JSON handling in queries and DML
180
interface JsonValue {
181
  toJSON(): any;
182
  toString(): string;
183
}
184
```
185

186
**Usage Examples:**
187

188
```javascript
189
// Insert JSON data
190
const jsonData = {
191
  name: 'Product A',
192
  price: 99.99,
193
  features: ['feature1', 'feature2'],
194
  metadata: {
195
    category: 'electronics',
196
    inStock: true
197
  }
198
};
199

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

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

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

215
// JSON queries with SQL/JSON functions
216
const result2 = await connection.execute(`
217
  SELECT JSON_VALUE(data, '$.name') as product_name,
218
         JSON_VALUE(data, '$.price') as price
219
  FROM products 
220
  WHERE JSON_EXISTS(data, '$.metadata.inStock' returning true)
221
`);
222
```
223

224
### Custom Type Conversions
225

226
Implement custom type handling for specialized data processing.
227

228
```javascript { .api }
229
// Fetch type handler for custom conversions
230
type FetchTypeHandler = (metadata: Metadata) => FetchTypeResult;
231

232
interface FetchTypeResult {
233
  type?: number;
234
  converter?: (value: any) => any;
235
}
236

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

240
interface DbObjectTypeResult {
241
  converter?: (value: any) => any;
242
}
243

244
interface Metadata {
245
  name: string;
246
  fetchType?: number;
247
  dbType?: number;
248
  byteSize?: number;
249
  precision?: number;
250
  scale?: number;
251
  nullable?: boolean;
252
}
253
```
254

255
**Usage Examples:**
256

257
```javascript
258
// Custom fetch type handler
259
const customFetchTypeHandler = (metadata) => {
260
  // Convert NUMBER columns to strings for precision
261
  if (metadata.dbType === oracledb.DB_TYPE_NUMBER && metadata.scale > 0) {
262
    return {
263
      type: oracledb.STRING,
264
      converter: (val) => val ? parseFloat(val) : null
265
    };
266
  }
267
  
268
  // Convert DATE columns to custom format
269
  if (metadata.dbType === oracledb.DB_TYPE_DATE) {
270
    return {
271
      converter: (val) => val ? val.toISOString() : null
272
    };
273
  }
274
};
275

276
// Apply globally
277
oracledb.fetchTypeHandler = customFetchTypeHandler;
278

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

286
// Custom DB object type handler
287
const customDbObjectTypeHandler = (metadata) => {
288
  // Convert object attributes to camelCase
289
  return {
290
    converter: (val) => {
291
      if (val && typeof val === 'object') {
292
        const converted = {};
293
        for (const [key, value] of Object.entries(val)) {
294
          const camelKey = key.toLowerCase().replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
295
          converted[camelKey] = value;
296
        }
297
        return converted;
298
      }
299
      return val;
300
    }
301
  };
302
};
303

304
oracledb.dbObjectTypeHandler = customDbObjectTypeHandler;
305
```
306

307
### Oracle-Specific Data Types
308

309
Handle Oracle's specialized data types including intervals and vectors.
310

311
```javascript { .api }
312
// Interval types
313
class IntervalYM {
314
  constructor(years: number, months: number);
315
  years: number;
316
  months: number;
317
  toString(): string;
318
}
319

320
class IntervalDS {
321
  constructor(days: number, hours: number, minutes: number, seconds: number, fseconds: number);
322
  days: number;
323
  hours: number;
324
  minutes: number;
325
  seconds: number;
326
  fseconds: number;
327
  toString(): string;
328
}
329

330
// Vector types (Oracle 23c+)
331
class SparseVector {
332
  constructor(dimensions: number, values: {[index: number]: number});
333
  dimensions: number;
334
  values: {[index: number]: number};
335
}
336

337
// Vector format constants
338
const VECTOR_FORMAT_FLOAT32 = 2;
339
const VECTOR_FORMAT_FLOAT64 = 3;
340
const VECTOR_FORMAT_INT8 = 4;
341
const VECTOR_FORMAT_BINARY = 5;
342
```
343

344
**Usage Examples:**
345

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

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

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

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

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

379
### Data Type Constants
380

381
```javascript { .api }
382
// Oracle database types
383
const DB_TYPE_BFILE = 114;
384
const DB_TYPE_BINARY_DOUBLE = 101;
385
const DB_TYPE_BINARY_FLOAT = 100;
386
const DB_TYPE_BINARY_INTEGER = 3;
387
const DB_TYPE_BLOB = 113;
388
const DB_TYPE_BOOLEAN = 252;
389
const DB_TYPE_CHAR = 96;
390
const DB_TYPE_CLOB = 112;
391
const DB_TYPE_CURSOR = 102;
392
const DB_TYPE_DATE = 12;
393
const DB_TYPE_INTERVAL_DS = 183;
394
const DB_TYPE_INTERVAL_YM = 182;
395
const DB_TYPE_JSON = 119;
396
const DB_TYPE_LONG = 8;
397
const DB_TYPE_LONG_NVARCHAR = 16;
398
const DB_TYPE_LONG_RAW = 24;
399
const DB_TYPE_NCHAR = 106;
400
const DB_TYPE_NCLOB = 111;
401
const DB_TYPE_NUMBER = 2;
402
const DB_TYPE_NVARCHAR = 1;
403
const DB_TYPE_OBJECT = 108;
404
const DB_TYPE_RAW = 23;
405
const DB_TYPE_ROWID = 104;
406
const DB_TYPE_TIMESTAMP = 187;
407
const DB_TYPE_TIMESTAMP_LTZ = 232;
408
const DB_TYPE_TIMESTAMP_TZ = 188;
409
const DB_TYPE_VARCHAR = 1;
410
const DB_TYPE_XMLTYPE = 109;
411
const DB_TYPE_VECTOR = 126;
412

413
// Type aliases for convenience
414
const BLOB = DB_TYPE_BLOB;
415
const BUFFER = DB_TYPE_RAW;
416
const CLOB = DB_TYPE_CLOB;
417
const CURSOR = DB_TYPE_CURSOR;
418
const DATE = DB_TYPE_TIMESTAMP;
419
const NCLOB = DB_TYPE_NCLOB;
420
const NUMBER = DB_TYPE_NUMBER;
421
const STRING = DB_TYPE_VARCHAR;
422
```