OracleStatement class

Represents a prepared SQL statement for executing queries and DML operations.

This class provides methods for executing SQL statements, fetching results, binding parameters, and performing batch operations. It supports both immediate execution and preparation followed by parameter binding.

Statement Lifecycle:

1. Create statement with execute or prepare
2. Bind parameters if needed with bindByPos or bindVar
3. Execute with executeStatement (if prepared)
4. Fetch results with rows, fetchOne, or fetchAll
5. Close statement with close when done

Example - Simple Query:

final stmt = await connection.execute(
  'SELECT id, name FROM employees WHERE dept_id = :1',
  params: [10],
);

try {
  await for (final row in stmt.rows()) {
    print('Employee: ${row['id']} - ${row['name']}');
  }
} finally {
  await stmt.close();
}

Example - Prepared Statement with Multiple Executions:

final stmt = await OracleStatement.prepare(
  dpiOracle: dpiOracle,
  connection: connection,
  context: context,
  sql: 'SELECT * FROM users WHERE id = :1',
);

try {
  for (final userId in [1, 2, 3]) {
    await stmt.bindByPos(1, userId);
    await stmt.executeStatement();

    final user = await stmt.fetchOne();
    print('User: $user');
  }
} finally {
  await stmt.close();
}

Example - Batch Insert:

final stmt = await OracleStatement.prepare(
  dpiOracle: dpiOracle,
  connection: connection,
  context: context,
  sql: 'INSERT INTO orders (id, customer, amount) VALUES (:1, :2, :3)',
);

try {
  final rowsAffected = await stmt.executemany([
    [1, 'Alice', 100.50],
    [2, 'Bob', 250.75],
    [3, 'Charlie', 175.25],
  ]);
  print('Inserted $rowsAffected rows');
} finally {
  await stmt.close();
}

Important:

• Always call close when done to free resources
• Use executemany for batch operations (much faster than loops)
• For large result sets, use rows stream instead of fetchAll

See also:

• OracleConnection.execute for creating statements
• OracleResultSet for result set operations
• OracleVariable for OUT parameter binding