I have a PL/SQL procedure that using 3 variables of
RECORD type as parameters, which i have to fill with my data (from c# application) to execute a procedure.
1st i've tried to build the same structure with this records via c# and pass as a parameter via Dapper, but it's not working since dapper works only with scalar data types.
Then i've tried to pass my data as arrays and to map them inside the procedure, but RECORD type can't accept sets of data like
a.NAME := ('aaaaaa','sdasd'); it works only this way:
a.NAME(1) := 'aaaaaa'; a.NAME(2) := 'sdasd';
The only thing that i have on my mind now is to generate such strings in the procedure text, but i'm feeling like there should be a better way. What am doing wrong?
P.S. i can't change the script that needs to be running after all, so i can't change the data type it accepts.
Update: To make it easier, let's say, i've decided to make an example:
We have 2 custom types
TYPE A_REC IS RECORD (NAME dbms_sql.Varchar2_Table); TYPE B_REC IS RECORD (NAME dbms_sql.Varchar2_Table , ID dbms_sql.Number_Table);
and a procedure, which might be used something like
declare a c_pck.a_rec; b c_pck.b_rec; begin a.NAME(1) := 'aaaaa'; b.NAME(1) := 'sssss'; b.NAME(2) := 'ddddd'; b.ID(1) := 111; b.ID(2) := 222; c_pck.pr(a => a, b => b); end;
How can i run this procedure via Dapper?
You need to handle two separate things here.
Here, you're going to need a fair amount of boilerplate.
You have to write mapping code for your UDTs, using the
IOracleCustomType interface. Here's the official documentation. This guide seems like a good resource.
Dapper has a hard-coded list of .NET types mapped to DbTypes.
You can add to this list, but only to the generic DbTypes.
To use database specific functionality, your parameter must implement
SqlMapper.ICustomQueryParameter (this is how the Table-valued
stuff for SQL server works). In your
AddParameter implementation, you can manually construct an
OracleParameter and set the UDT typename correctly.
The return value should be handled just fine by Dapper, since this is just mapping a result set like