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

Answer 2

I don't know Dapper, but - from Oracle's point of view - one option to do that is to return affected IDs into a collection. The following example uses built-in datatype, sys.odcinumberlist (you can create your own, if you want). See line #7:

SQL> set serveroutput on;
SQL> declare
  2    l_col sys.odcinumberlist;
  3  begin
  4    update test set
  5      sal = sal + 100
  6      where deptno = 10
  7      returning empno bulk collect into l_col;        --> here
  8
  9    for i in 1 .. l_col.count loop
 10      dbms_output.put_line(l_col(i));
 11    end loop;
 12  end;
 13  /
7782              --> these IDs have been affected
7839
7934

PL/SQL procedure successfully completed.

SQL>

I hope you can adjust that code so that it can be used in Dapper. Unfortunately, as I said, I have no idea how to do that part of the job.