Oracle Get more than one value as output parameter using Dapper

Question

Here's my code:

string cmd = "UPDATE MyTable SET Status='A' WHERE CaseId='123' RETURNING DocId INTO :UpdatedDocId";
DynamicParameters para = new DynamicParameters();
para.Add("UpdatedDocId", direction: ParameterDirection.Output, dbType: DbType.String, size: 50);
await db.ExecuteAsync(cmd, para);
var tempOutputValue = para.Get<dynamic>("UpdatedDocId");

If there is only one row updated, I can get the updated DocId as tempOutputValue correctly, but if there are two or more rows updated, I got error like this: ORA-24369: Required callbacks not registered for one or more bind handles

Is there any way I can get all updated DocIds?

Answer 1

In Oracle, you would use RETURNING column_name BULK COLLECT INTO collection_variable.

Which, if you were going to implement it in PL/SQL would be:

DECLARE
  TYPE docid_list IS TABLE OF MyTable.DocID%TYPE;
  v_UpdatedDocId docid_list;
BEGIN
  UPDATE MyTable
  SET    Status='A'
  WHERE  CaseId='123'
  RETURNING DocId BULK COLLECT INTO v_UpdatedDocId;

  FOR i IN 1 .. v_UpdatedDocId.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_UpdatedDocId(i));
  END LOOP;
END;
/

You would need to change your code from INTO to BULK COLLECT INTO and pass it into an bind variable that could accept an array.

However, C# does not support passing Oracle SQL collection data types so it may not be possible (no idea why, you can do it in other languages but the C# drivers do not support it). It does support PL/SQL associative arrays so you may be able to BULK COLLECT INTO an SQL collection and then convert the SQL collection to a PL/SQL associative array and return that.

First, within the database, create a package defining the associative array:

CREATE PACKAGE package_name AS
  TYPE STRING_MAP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
END;
/

Then you can use something like (untested as I don't have C#, based on code from this question):

string cmd = @"DECLARE
  v_array package_name.string_map;
BEGIN
  UPDATE MyTable
  SET    Status='A'
  WHERE  CaseId='123'
  RETURNING DocId BULK COLLECT INTO v_array;

  :UpdatedDocId := v_array;
END;";
DynamicParameters para = new DynamicParameters();
para.Add(
    "UpdatedDocId",
    direction: ParameterDirection.Output,
    CollectionType = OracleCollectionType.PLSQLAssociativeArray,
    UdtTypeName = "PACKAGE_NAME.STRING_MAP"
);
await db.ExecuteAsync(cmd, para);

Oracle fiddle