0

I already have working examples like the following, to essentially get results from a SQL query from C#:

  Order GetOrder(int orderId)
  {
    List<OracleParameter> parameters = new()
    {
        new("Id", OracleDbType.Varchar2, orderId, ParameterDirection.Input)
    };
    OracleDataReader reader = await GetDataReaderAsync(connection, "my_package.GetOrder", parameters);
    ... //read the results

======

PROCEDURE GetOrder(Id in number,results out sys_refcursor) IS
BEGIN 
Open results for
Select 
buyer, orderDate, cost
from Order 
WHERE Id = Id
END GetOrder;

But I am stuck trying to make a version GetOrders(List<int> orderIds). I have already come across OracleParameter.CollectionType and I have read several questions:

Each addresses a different piece but I cannot anywhere find an end-to-end example. I'm not an Oracle guy though - though I can write queries, creating a procedure I rely on finding an example and copying it with modifications. This stuff about associative arrays and defining custom PLSQL types is just confusing me, especially if I have to create a type for each procedure... I only understand simple scalar DB types not passing tables as parameters.

Could anyone show me (or point me to the duplicate answer I can't find) a rough outline of how I'd create a GetOrders version of the above - the changes made in C# & PL/SQL to handle the List parameter?

Mr. Boy
  • 60,845
  • 93
  • 320
  • 589
  • Does this answer your question? [How to pass a table-valued parameter from C# to Oracle stored procedure](https://stackoverflow.com/questions/12712355/how-to-pass-a-table-valued-parameter-from-c-sharp-to-oracle-stored-procedure) – Peter Csala Nov 18 '22 at 13:52
  • @PeterCsala no... I can barely understand that question but it is about custom types and table mapping, not passing simple array/list data. My question is a basic "how do I do X" and theirs seems to be a more specific question about one aspect of this once you understand the basics of X – Mr. Boy Nov 18 '22 at 14:05
  • MSSQL does support a [table valued parameter](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16) which allows you to define a table type with a single column and pass it to a stored procedure. So, you can map your `List` to this custom table type. According to the above SO topic it seems like that same is achievable via UDT in Oracle. – Peter Csala Nov 18 '22 at 14:12
  • 1
    @PeterCsala yes, the topics I link to myself refer to this sort of thing (the PLSQLAssociativeArray thing, I think) - I just can't piece the various bits together. – Mr. Boy Nov 18 '22 at 14:14

0 Answers0