callproc method

Future<List> callproc(
  1. String name, {
  2. List? parameters,
  3. Map<String, OracleVariable>? keywordParameters,
})

Call a stored procedure or function with IN and OUT parameters.

Executes a PL/SQL procedure or function and returns values from OUT/INOUT parameters. Similar to python-oracledb's callproc().

Parameters:

  • name: Procedure or function name (can include schema: 'SCHEMA.PROC_NAME')
  • parameters: List of IN parameters (positional order)
  • keywordParameters: Map of names to OracleVariable for OUT/INOUT parameters

Returns: A list of values from OUT parameters in the order they appear in keywordParameters.

Example - Procedure with OUT Parameter:

// PL/SQL: PROCEDURE get_employee_info(emp_id IN NUMBER, emp_name OUT VARCHAR2)

final nameVar = await connection.createVar(
  oracleType: DPI_ORACLE_TYPE_VARCHAR,
  nativeType: DPI_NATIVE_TYPE_BYTES,
  size: 100,
);

final results = await connection.callproc(
  'get_employee_info',
  parameters: [employeeId],  // IN parameter
  keywordParameters: {'emp_name': nameVar},  // OUT parameter
);

print('Employee name: ${results[0]}');

Example - Function Returning Value:

// PL/SQL: FUNCTION calculate_bonus(salary NUMBER) RETURN NUMBER

final returnVar = await connection.createVar(
  oracleType: DPI_ORACLE_TYPE_NUMBER,
  nativeType: DPI_NATIVE_TYPE_DOUBLE,
);

final results = await connection.callproc(
  'calculate_bonus',
  parameters: [50000],
  keywordParameters: {'return': returnVar},
);

final bonus = results[0];
print('Bonus: \$$bonus');

Example - Multiple OUT Parameters:

final nameVar = await connection.createVar(
  oracleType: DPI_ORACLE_TYPE_VARCHAR,
  nativeType: DPI_NATIVE_TYPE_BYTES,
  size: 100,
);
final salaryVar = await connection.createVar(
  oracleType: DPI_ORACLE_TYPE_NUMBER,
  nativeType: DPI_NATIVE_TYPE_DOUBLE,
);

final results = await connection.callproc(
  'HR.get_employee_details',
  parameters: [101],  // emp_id IN
  keywordParameters: {
    'name': nameVar,     // OUT
    'salary': salaryVar, // OUT
  },
);

print('Name: ${results[0]}, Salary: ${results[1]}');

Important: Don't forget to call commit if the procedure modifies data.

See also:

  • createVar to create OUT parameter variables
  • execute for direct SQL/PL/SQL execution

Implementation

Future<List<dynamic>> callproc(
  String name, {
  List<dynamic>? parameters,
  Map<String, OracleVariable>? keywordParameters,
}) async {
  _ensureConnected();

  // Build PL/SQL call
  final paramCount = parameters?.length ?? 0;
  final outParamCount = keywordParameters?.length ?? 0;

  final placeholders = List.generate(paramCount + outParamCount, (i) => ':${i + 1}').join(', ');
  final sql = 'BEGIN $name($placeholders); END;';

  // Prepare statement (don't execute yet)
  final statement = await OracleStatement.prepare(
    dpiOracle: _dpiOracle,
    connection: _connectionPtr.value,
    context: _context,
    sql: sql,
  );

  try {
    // Bind input parameters first
    if (parameters != null) {
      for (var i = 0; i < parameters.length; i++) {
        await statement.bindByPos(i + 1, parameters[i]);
      }
    }

    // Bind OUT parameters
    if (keywordParameters != null) {
      var pos = paramCount + 1;
      for (final variable in keywordParameters.values) {
        await statement.bindVar(pos, variable);
        pos++;
      }
    }

    // Now execute the statement after all bindings are set
    await statement.executeStatement();

    // Collect OUT parameter values
    final results = <dynamic>[];
    if (keywordParameters != null) {
      for (final variable in keywordParameters.values) {
        final value = variable.getValue();
        results.add(value);
      }
    }

    return results;
  } finally {
    await statement.close();
  }
}