Calling a function with user defined type parameters (Oracle ODP.NET)

Question

I'm using a function :

fu_has_permissions(udt_person('johny','superman'),'fly_away')

udt_person is a user defined type :

create or replace TYPE udt_person AS OBJECT 
(name VARCHAR2(3), 
id VARCHAR2(18));

I want to use bind variables whan calling this function, but i'm not really sure what am i doing wrong ... Here's the code :

......
OracleParameter udtPersParam = new OracleParameter();
udtPersParam.ParameterName = ":pUdtPers";
udtPersParam.UdtTypeName = "UDT_PERS";
string[] paramValues = { name, id };
udtPersParam.Value = paramValues;
OracleParameter pAction = new OracleParameter(":pAction", OracleDbType.Varchar2, 255);
pAction.Value = action;

parameters.Add(udtPartParam);
parameters.Add(pAction);

try
{
_loginOdr = DBFacade.ExecuteSelectQuery("select fu_has_permissions(:pUdtPart, :pAction) from dual", parameters);
}

Thanks!

What error do you get? Is it a typo that UdtTypeName doesn't match the Oracle type name? — Alex Poole
– Alex Poole, Commented Nov 12, 2010 at 8:09
"Value does not fall within the expected range.", at line udtPersParam.Value = paramValues; — maephisto
– maephisto, Commented Nov 12, 2010 at 8:47

Harrison · Accepted Answer · 2010-11-18 18:37:06Z

4

the udt type must be a class that implements IOracleCustomType and/or IOracleCustomTypeFactory, IOracleArrayTypeFactory

unfortuntately you cannot just create a string array and pass it in

look in the odp.net samples that come with the odp installation %ora_home%\client_1\odp.net\samples\4\UDT

also check out these links for samples and walkthroughs http://developergeeks.com/article/35/working-with-user-defined-type-oracle-udt-and-custom-type-using-odp-net-11g http://www.codeproject.com/KB/database/ORACLE_UDT.aspx and http://st-curriculum.oracle.com/obe/db/hol08/dotnet/udt/udt_otn.htm

answered Nov 18, 2010 at 18:37

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stealth Rabbi Over a year ago

The path to the samples was different in my case of 11.2: %ora_home%\product\11.2.0\client_1_x86\ODACsamples\odp.net\4\UDT

Alex Poole · Accepted Answer · 2010-11-17 12:35:46Z

1

Don't know anything about ODP.Net really, but the error suggests that it doesn't like you trying to use a string array as the value for an Oracle parameter. Which doesn't sound unreasonable.

A quick google of 'odp.net object varchar2' gave this OTN forum post as the first result; it includes an example of using an object about half-way down, including converting to and from Oracle object types.

answered Nov 17, 2010 at 12:35

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Comments

PeteH · Accepted Answer · 2011-10-21 08:14:01Z

if I were you I'd have a look at the ODP add-in to Visual Studio. With this you can connect to your database, select a UDT on the database, and "Generate Custom Class" to get a .net class you can use.

Look inside the class and you'll see what Harrison means. Pay particular attention to the OracleObjectMappingAttributes on top of the properties, and the overrides of To/FromCustomObject.

When you construct your OracleCommand, the OracleParameter.Value needs to be a class of this type.

That should get you started at a high level. A word of warning, though. The code generated by ODP is ugly to say the least - we're on the point of ditching all ODP-generated classes in our own scenario. But you'll need to understand what things like IOracleCustomType, IOracleCustomTypeFactory, IOracleArrayTypeFactory, INullable are before you'll be in a position to do this.

Incidentally since your specific question surrounds arrays, you might want to look at Oracle NTYPEs here, rather than TYPEs.

Collectives™ on Stack Overflow

Calling a function with user defined type parameters (Oracle ODP.NET)

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related