executemany method

Implementation

Future<dynamic> executemany(
  List<List<dynamic>> parameters, {
  bool batchErrors = false,
  bool arrayDmlRowCounts = false,
}) async {
  if (_disposed) {
    throw OracleStatementException('Statement has been closed', sql: _sql);
  }

  if (parameters.isEmpty) {
    return arrayDmlRowCounts ? <int>[] : 0;
  }

  // Validate that all parameter sets have the same length
  final expectedLength = parameters.first.length;
  for (var i = 1; i < parameters.length; i++) {
    if (parameters[i].length != expectedLength) {
      throw OracleStatementException(
        'All parameter sets must have the same length. '
        'Expected $expectedLength parameters, but parameter set $i has ${parameters[i].length}',
        sql: _sql,
      );
    }
  }

  final numRows = parameters.length;
  final numParams = expectedLength;
  final arrayVars = <OracleVariable>[];

  try {
    // Note: Batch error mode is handled internally by ODPI-C
    // The batchErrors parameter controls whether to collect errors

    // Create array variables for each parameter position
    for (var paramPos = 0; paramPos < numParams; paramPos++) {
      final position = paramPos + 1; // 1-based

      // Determine type from first non-null value in this position
      int oracleType;
      int nativeType;
      int size = 0;

      dynamic sampleValue;
      for (var rowIdx = 0; rowIdx < numRows; rowIdx++) {
        final value = parameters[rowIdx][paramPos];
        if (value != null) {
          sampleValue = value;
          break;
        }
      }

      // Determine types based on sample value
      if (sampleValue == null) {
        // All nulls - default to string
        oracleType = DPI_ORACLE_TYPE_VARCHAR;
        nativeType = DPI_NATIVE_TYPE_BYTES;
        size = 1;
      } else if (sampleValue is String) {
        oracleType = DPI_ORACLE_TYPE_VARCHAR;
        nativeType = DPI_NATIVE_TYPE_BYTES;
        // Find max string length in this column (in bytes for UTF-8)
        size = 1;
        for (var rowIdx = 0; rowIdx < numRows; rowIdx++) {
          final value = parameters[rowIdx][paramPos];
          if (value is String && value.isNotEmpty) {
            final byteLength = StringUtils.getUtf8ByteLength(value);
            if (byteLength > size) {
              size = byteLength;
            }
          }
        }
      } else if (sampleValue is int) {
        oracleType = DPI_ORACLE_TYPE_NUMBER;
        nativeType = DPI_NATIVE_TYPE_INT64;
      } else if (sampleValue is double) {
        oracleType = DPI_ORACLE_TYPE_NUMBER;
        nativeType = DPI_NATIVE_TYPE_DOUBLE;
      } else if (sampleValue is DateTime) {
        oracleType = DPI_ORACLE_TYPE_TIMESTAMP;
        nativeType = DPI_NATIVE_TYPE_TIMESTAMP;
      } else if (sampleValue is Duration) {
        oracleType = DPI_ORACLE_TYPE_INTERVAL_DS;
        nativeType = DPI_NATIVE_TYPE_INTERVAL_DS;
      } else if (sampleValue is bool) {
        oracleType = DPI_ORACLE_TYPE_BOOLEAN;
        nativeType = DPI_NATIVE_TYPE_BOOLEAN;
      } else if (sampleValue is Map && sampleValue.containsKey('years') && sampleValue.containsKey('months')) {
        // INTERVAL YEAR TO MONTH
        oracleType = DPI_ORACLE_TYPE_INTERVAL_YM;
        nativeType = DPI_NATIVE_TYPE_INTERVAL_YM;
      } else if (sampleValue is OracleLob) {
        // LOB type
        if (sampleValue.lobType == LobType.clob) {
          oracleType = DPI_ORACLE_TYPE_CLOB;
          nativeType = DPI_NATIVE_TYPE_LOB;
        } else if (sampleValue.lobType == LobType.blob) {
          oracleType = DPI_ORACLE_TYPE_BLOB;
          nativeType = DPI_NATIVE_TYPE_LOB;
        } else {
          oracleType = DPI_ORACLE_TYPE_NCLOB;
          nativeType = DPI_NATIVE_TYPE_LOB;
        }
      } else {
        throw OracleStatementException(
          'Unsupported parameter type at position $position: ${sampleValue.runtimeType}',
          sql: _sql,
        );
      }

      // Create array variable
      final arrayVar = await OracleVariable.create(
        dpiOracle: _dpiOracle,
        connection: _connection,
        context: _context,
        oracleType: oracleType,
        nativeType: nativeType,
        size: size,
        arraySize: numRows,
        direction: BindDirection.input,
      );

      arrayVars.add(arrayVar);

      // Set values for all rows in this array
      for (var rowIdx = 0; rowIdx < numRows; rowIdx++) {
        final value = parameters[rowIdx][paramPos];
        arrayVar.setArrayValue(rowIdx, value);
      }

      // Bind the array variable to the statement
      final bindResult = _dpiOracle.dpiStmt_bindByPos(
        _statementPtr.value,
        position,
        arrayVar.varPointer,
      );

      if (bindResult == DPI_FAILURE) {
        throw OracleStatementException(
          'Failed to bind array variable at position $position',
          sql: _sql,
        );
      }
    }

    // Execute all rows in a single call (true array DML)
    // Set execution mode based on options
    int execMode = DPI_MODE_EXEC_DEFAULT;
    if (arrayDmlRowCounts) {
      execMode |= DPI_MODE_EXEC_ARRAY_DML_ROWCOUNTS;
    }
    if (batchErrors) {
      execMode |= DPI_MODE_EXEC_BATCH_ERRORS;
    }

    final execResult = _dpiOracle.dpiStmt_executeMany(
      _statementPtr.value,
      execMode,
      numRows,
    );

    if (execResult == DPI_FAILURE && !batchErrors) {
      final errorInfo = _memoryManager.allocate<dpiErrorInfo>(sizeOf<dpiErrorInfo>());
      _dpiOracle.dpiContext_getError(_context, errorInfo);
      final errorMsg = StringUtils.fromNativeUtf8(errorInfo.ref.message.cast<Char>());
      final errorCode = errorInfo.ref.code;

      throw OracleStatementException(
        'Failed to execute batch operation',
        errorCode: errorCode,
        errorMessage: errorMsg,
        sql: _sql,
      );
    }

    _executed = true;

    // Get row counts if requested
    if (arrayDmlRowCounts) {
      final numRowCounts = _memoryManager.allocate<Uint32>(sizeOf<Uint32>());
      final rowCountsPtr = _memoryManager.allocate<Pointer<Uint64>>(sizeOf<Pointer<Uint64>>());

      final rowCountResult = _dpiOracle.dpiStmt_getRowCounts(
        _statementPtr.value,
        numRowCounts,
        rowCountsPtr,
      );

      if (rowCountResult == DPI_FAILURE) {
        throw OracleStatementException(
          'Failed to get row counts',
          sql: _sql,
        );
      }

      final rowCounts = <int>[];
      final count = numRowCounts.value;
      for (var i = 0; i < count; i++) {
        final rowCountValue = (rowCountsPtr.value + i).value;
        rowCounts.add(rowCountValue);
      }

      return rowCounts;
    } else {
      // Get total row count
      final rowCount = _memoryManager.allocate<Uint64>(sizeOf<Uint64>());
      _dpiOracle.dpiStmt_getRowCount(_statementPtr.value, rowCount);
      return rowCount.value;
    }
  } finally {
    // Dispose of array variables
    for (final arrayVar in arrayVars) {
      arrayVar.dispose();
    }
  }
}