0
# SQL Execution
1

2
Complete SQL statement execution capabilities including queries, DML, DDL, and stored procedure execution with parameter binding.
3

4
## Capabilities
5

6
### Execute
7

8
Executes a SQL statement with optional parameter binding and configuration options.
9

10
```javascript { .api }
11
/**
12
 * Executes a SQL statement
13
 * @param sql - SQL statement to execute
14
 * @param binds - Optional bind parameters
15
 * @param options - Optional execution options
16
 * @returns Promise resolving to execution result
17
 */
18
execute(sql: string, binds?: BindParameters, options?: ExecuteOptions): Promise<Result>;
19

20
type BindParameters = BindParametersObject | BindParametersArray;
21
type BindParametersObject = {[key: string]: BindValue};
22
type BindParametersArray = BindValue[];
23

24
interface BindValue {
25
  val?: any;
26
  dir?: number;
27
  type?: number;
28
  maxSize?: number;
29
  maxArraySize?: number;
30
}
31

32
interface ExecuteOptions {
33
  autoCommit?: boolean;
34
  fetchArraySize?: number;
35
  fetchInfo?: {[key: string]: FetchInfo};
36
  fetchTypeHandler?: FetchTypeHandler;
37
  keepInStmtCache?: boolean;
38
  maxRows?: number;
39
  outFormat?: number;
40
  prefetchRows?: number;
41
  resultSet?: boolean;
42
}
43

44
interface Result {
45
  rows?: any[][];
46
  metaData?: Metadata[];
47
  outBinds?: {[key: string]: any} | any[];
48
  rowsAffected?: number;
49
  lastRowid?: string;
50
  resultSet?: ResultSet;
51
  implicitResults?: ResultSet[];
52
  warning?: ExecuteWarning;
53
}
54

55
interface Metadata {
56
  name: string;
57
  fetchType?: number;
58
  dbType?: number;
59
  byteSize?: number;
60
  precision?: number;
61
  scale?: number;
62
  nullable?: boolean;
63
}
64

65
type FetchTypeHandler = (metadata: Metadata) => any;
66

67
interface FetchInfo {
68
  type?: number;
69
  converter?: (value: any) => any;
70
}
71

72
interface ExecuteWarning {
73
  message: string;
74
  offset: number;
75
}
76
```
77

78
**Usage Examples:**
79

80
```javascript
81
const oracledb = require('oracledb');
82

83
// Simple query
84
const result = await connection.execute(
85
  'SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30'
86
);
87
console.log(result.rows);
88

89
// Query with bind parameters (positional)
90
const result = await connection.execute(
91
  'SELECT * FROM employees WHERE department_id = :1 AND salary > :2',
92
  [30, 5000]
93
);
94

95
// Query with bind parameters (named)
96
const result = await connection.execute(
97
  'SELECT * FROM employees WHERE department_id = :dept AND salary > :sal',
98
  { dept: 30, sal: 5000 }
99
);
100

101
// DML with OUT binds
102
const result = await connection.execute(
103
  `BEGIN
104
     INSERT INTO employees (employee_id, first_name, last_name) 
105
     VALUES (employee_seq.NEXTVAL, :fname, :lname)
106
     RETURNING employee_id INTO :id;
107
   END;`,
108
  {
109
    fname: 'John',
110
    lname: 'Doe',
111
    id: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER }
112
  }
113
);
114
console.log('New employee ID:', result.outBinds.id);
115

116
// Configure result format
117
const result = await connection.execute(
118
  'SELECT employee_id, first_name FROM employees',
119
  [],
120
  { outFormat: oracledb.OUT_FORMAT_OBJECT }
121
);
122
console.log(result.rows[0].FIRST_NAME);
123
```
124

125
### Execute Many
126

127
Executes a SQL statement multiple times with different bind parameter sets, optimized for bulk operations.
128

129
```javascript { .api }
130
/**
131
 * Executes a statement multiple times with different bind sets
132
 * @param sql - SQL statement to execute
133
 * @param bindParams - Array of bind parameter sets or number of iterations
134
 * @param options - Optional execution options
135
 * @returns Promise resolving to execution result
136
 */
137
executeMany(sql: string, bindParams: BindParameters[] | number, options?: ExecuteManyOptions): Promise<ResultMany>;
138

139
interface ExecuteManyOptions {
140
  autoCommit?: boolean;
141
  bindDefs?: BindDefinition[];
142
  batchErrors?: boolean;
143
  dmlRowCounts?: boolean;
144
  keepInStmtCache?: boolean;
145
}
146

147
interface BindDefinition {
148
  dir?: number;
149
  type: number;
150
  maxSize?: number;
151
  maxArraySize?: number;
152
}
153

154
interface ResultMany {
155
  rowsAffected?: number[];
156
  outBinds?: any[][];
157
  batchErrors?: BatchError[];
158
  dmlRowCounts?: number[];
159
  warning?: ExecuteWarning;
160
}
161

162
interface BatchError {
163
  error: Error;
164
  offset: number;
165
}
166
```
167

168
**Usage Examples:**
169

170
```javascript
171
// Bulk insert
172
const data = [
173
  ['John', 'Doe', 50000],
174
  ['Jane', 'Smith', 60000],
175
  ['Bob', 'Johnson', 55000]
176
];
177

178
const result = await connection.executeMany(
179
  'INSERT INTO employees (first_name, last_name, salary) VALUES (:1, :2, :3)',
180
  data,
181
  { autoCommit: true, dmlRowCounts: true }
182
);
183
console.log('Rows inserted:', result.rowsAffected);
184

185
// Bulk insert with bind definitions for performance
186
const result = await connection.executeMany(
187
  'INSERT INTO employees (first_name, last_name, salary) VALUES (:fname, :lname, :sal)',
188
  [
189
    { fname: 'John', lname: 'Doe', sal: 50000 },
190
    { fname: 'Jane', lname: 'Smith', sal: 60000 }
191
  ],
192
  {
193
    bindDefs: [
194
      { type: oracledb.STRING, maxSize: 50 },
195
      { type: oracledb.STRING, maxSize: 50 },
196
      { type: oracledb.NUMBER }
197
    ],
198
    autoCommit: true
199
  }
200
);
201

202
// Handle batch errors
203
const result = await connection.executeMany(
204
  'INSERT INTO employees (employee_id, first_name) VALUES (:1, :2)',
205
  [[1, 'John'], [1, 'Jane']],  // Second will fail due to duplicate key
206
  { batchErrors: true }
207
);
208

209
if (result.batchErrors) {
210
  for (const error of result.batchErrors) {
211
    console.log(`Error at offset ${error.offset}:`, error.error.message);
212
  }
213
}
214
```
215

216
### Query Stream
217

218
Creates a readable stream for large result sets to avoid memory consumption issues.
219

220
```javascript { .api }
221
/**
222
 * Creates a readable stream for query results
223
 * @param sql - SQL query statement
224
 * @param binds - Optional bind parameters
225
 * @param options - Optional stream options
226
 * @returns QueryStream instance
227
 */
228
queryStream(sql: string, binds?: BindParameters, options?: StreamOptions): QueryStream;
229

230
interface StreamOptions extends ExecuteOptions {
231
  fetchArraySize?: number;
232
}
233

234
interface QueryStream extends NodeJS.ReadableStream {
235
  destroy(): void;
236
  pause(): QueryStream;
237
  resume(): QueryStream;
238
}
239
```
240

241
**Usage Examples:**
242

243
```javascript
244
const stream = connection.queryStream(
245
  'SELECT * FROM large_table WHERE date_col > :1',
246
  [new Date('2023-01-01')]
247
);
248

249
stream.on('data', (row) => {
250
  console.log('Row:', row);
251
});
252

253
stream.on('end', () => {
254
  console.log('Query completed');
255
});
256

257
stream.on('error', (err) => {
258
  console.error('Stream error:', err);
259
});
260

261
// Process large datasets without loading everything into memory
262
stream.on('data', (row) => {
263
  // Process each row individually
264
  processRow(row);
265
});
266
```
267

268
### Get Statement Info
269

270
Retrieves metadata information about a SQL statement without executing it.
271

272
```javascript { .api }
273
/**
274
 * Gets information about a SQL statement
275
 * @param sql - SQL statement to analyze
276
 * @returns Promise resolving to statement information
277
 */
278
getStatementInfo(sql: string): Promise<StatementInfo>;
279

280
interface StatementInfo {
281
  statementType: number;
282
  bindNames: string[];
283
  metaData?: Metadata[];
284
}
285
```
286

287
**Usage Examples:**
288

289
```javascript
290
// Analyze a SQL statement
291
const info = await connection.getStatementInfo(
292
  'SELECT employee_id, first_name FROM employees WHERE department_id = :dept'
293
);
294

295
console.log('Statement type:', info.statementType);
296
console.log('Bind variables:', info.bindNames); // ['dept']
297
console.log('Metadata:', info.metaData);
298

299
// Check if statement is a SELECT
300
if (info.statementType === oracledb.STMT_TYPE_SELECT) {
301
  console.log('This is a SELECT statement');
302
}
303
```
304

305
### Result Set Handling
306

307
Handle large result sets with cursor-based navigation.
308

309
```javascript { .api }
310
interface ResultSet {
311
  close(): Promise<void>;
312
  getRow(): Promise<any>;
313
  getRows(numRows?: number): Promise<any[]>;
314
  toQueryStream(): QueryStream;
315
  metaData: Metadata[];
316
}
317
```
318

319
**Usage Examples:**
320

321
```javascript
322
// Get a result set instead of all rows
323
const result = await connection.execute(
324
  'SELECT * FROM large_table',
325
  [],
326
  { resultSet: true }
327
);
328

329
const resultSet = result.resultSet;
330

331
// Fetch rows one at a time
332
let row;
333
while ((row = await resultSet.getRow())) {
334
  console.log('Row:', row);
335
}
336

337
// Or fetch in batches
338
const rows = await resultSet.getRows(100);
339
console.log('First 100 rows:', rows);
340

341
// Always close the result set
342
await resultSet.close();
343

344
// Convert to stream
345
const stream = resultSet.toQueryStream();
346
stream.on('data', (row) => console.log(row));
347
```
348

349
## Bind Parameter Types
350

351
```javascript { .api }
352
// Bind directions
353
const BIND_IN = 3001;
354
const BIND_INOUT = 3002;
355
const BIND_OUT = 3003;
356

357
// Common bind value patterns
358
interface SimpleBindValue {
359
  val: any;                    // The bind value
360
  dir?: number;               // Bind direction (default: BIND_IN)
361
  type?: number;              // Data type (auto-detected if not specified)
362
  maxSize?: number;           // Maximum size for OUT/INOUT binds
363
}
364

365
interface ArrayBindValue {
366
  val: any[];                 // Array of values for executeMany
367
  dir?: number;
368
  type?: number;
369
  maxArraySize?: number;      // Maximum array size
370
}
371
```
372

373
## Data Type Constants
374

375
```javascript { .api }
376
// Oracle database types
377
const DB_TYPE_VARCHAR = 1;
378
const DB_TYPE_NUMBER = 2;
379
const DB_TYPE_DATE = 12;
380
const DB_TYPE_TIMESTAMP = 187;
381
const DB_TYPE_CLOB = 112;
382
const DB_TYPE_BLOB = 113;
383
const DB_TYPE_JSON = 119;
384

385
// Type aliases
386
const STRING = DB_TYPE_VARCHAR;
387
const NUMBER = DB_TYPE_NUMBER;
388
const DATE = DB_TYPE_TIMESTAMP;
389
const CLOB = DB_TYPE_CLOB;
390
const BLOB = DB_TYPE_BLOB;
391
```