PLSQL Record type support for associative arrays

[gpgpublickey]

In the following scenario:

Stored procedures or functions trying to pass table valued parameters / associative arrays / PL/SQL Index-By Tables are not supporting the PLSQL RECORD type, many applications use this strategy to use multidimensional arrays, currently the ODP.NET Core is not able to work with this kind of associative arrays.

Real world problem: Use plsql records in combination of associative arrays is really often common strategy used in PLSQL to handle data, so each time you found something like this (and is very common to found) you will need to write wrappers into db or modify existing sp's or functions (something that is not always possible depending on project constraints)

Is there any expected date to implement the support for RECORDS or any well known workaround to make possible use ODP.NET Core in this scenario?

[alexkeh]

This feature cannot be used today unless the RECORD is changed into some other type, such as a custom user-defined type.

There hasn't been too many customer requests for this feature. If developers vote for this feature, the Oracle .NET team will look closer at implementing native ODP.NET RECORD data type support.

[gpgpublickey]

Hi @alexkeh any recommended approach regarding this replacement of RECORD to an UDT type? I can't modify the original SPs because of backward compatibility, so I will need to go with a wrapper. But would like to hear recommended strategies to parse the RECORD into an OBJECT and viceversa.

Where is possible to vote for this request? is any existent request for it?

[alexkeh]

Developers can give a thumbs up to the original request you made up top to vote for the request. You'll see this commonly used with other ODP.NET and .NET feature requests. People can also comment on the thread on their own need as well to provide added detail on their need and their preferences. Here's the current Oracle .NET GitHub issue list organized by thumbs up votes.

This Oracle forum thread on using RECORDs with ODP.NET provide more details on possible workarounds. Unmanaged ODP.NET was available at the time, but the general advice applies to ODP.NET Core even today. Using a wrapper is one of the possible workarounds.

[gpgpublickey]

In order to look for a workaround, I decided to go with a wrapper, so now I'm trying to return a nested array of an UDT object, the command is input/output direction. But when I try to execute it I get a System.InvalidCastException: Column contains NULL data

But the UDT type defined in my c# class it has just string types, same in my UDT type in db, create or replace TYPE FOO_TYPE AS OBJECT ( my_foo_bar VARCHAR2(20));

Any idea why I'm getting such kind of error? I'm trying to follow the nested types with UDT docs example, but in a different way since I need an input/output scenario. cc: @alexkeh any advise here? or maybe something to tag in order to help?

Thx

[alexkeh]

There are additional ODP.NET UDT samples on GitHub.

The Oracle Developer Tools for Visual Studio has an UDT custom class code generation wizard to make it easy to set up and use UDTs in .NET.

[gpgpublickey]

Yes I was using the examples there, but nothing similar for my scenario, I will give a try to the VS Oracle dev tools to see if that solve my issue. Thanks, I would like to suggest add an example of input/output parameters for nested + UDTs. Thanks

[gpgpublickey]

I'm able to retrieve it correctly using the auto generated udt types created with the oracle developer tools extension for visual studio, thanks for the support!

I will explain here what workaround I decided to follow and why:

I found mostly two recommended approaches in many topics in forums, blogs, etc. The main problem as is described in the first post on this issue is lack of native support of PLSQL RECORD type in data providers (not just odp.net, i didn't found providers supporting this plsql special type). The workarounds are: 1- db wrapper + sys_refcursors 2- db wrapper + nested array + UDT type

Option 1- Is just a good option if your procedure or function needs to support just OUTPUT / RETURN way, since is not posible to send sys_refcursors as INPUTs from ODP.NET, at least I didn't found an intermediate complexity level to go through that vs time efforts.

Option 2- Use a wrapper replacing the associative array for a nested array, and replace the PLSQL Record type by an UDT type, aditionally, you can add an extra parameter to receive the original RECORD type in your wrapper, since is possible to write raw sql to declare and define the record type as part of the Command configuration with ODP.NET, so you can send the RECORD type with any required data, pass it to the original functionality and then parse the result to the UDT nested array as part of your wrapper body implementation. I didn't check this approach with functions, but for functions probably option 1 is easier to use since you don't need to be worried about INPUT / OUTPUT parameters, you can build the record type in a declare block in you client class, then get the result as a sys_refcursor, but option 2 is something to have in mind if you want something more object oriented.

Hope this will help somebody in the future ) have a nice day!

[christianshay]

Careful with using UDTs unneccesarily. There can be performance implications. Make you evaluate the performance and make sure it scales as you need it. In performance sensitive use cases I would not use them.