Oracle user defined types (UDT) in stored procedures

[Jupp]

Hi everybody,

we have an Oracle database with stored procedures that have UDT's as IN/OUT-parameter.

The environment ist VS2010 (Telerik OpenAccess Class Library Project) , C#, Oracle11gR2, ODP.net 1120320, OA Q3_2012-SP1

Currently I don't get it done.
After the model creation wizard (populate from DB) has run Visual Studio shows errors:




The specified clr type 'Object' is not valid for the parameter PAR_RESULT of the procedure 'VFADMIN2_SCHEMA'.'VE_PA_ASSC'.'PR_CHANGE_A_RDJC_RESTRICT'.

The SP in the database is defined as follows:

procedure    PR_CHANGE_A_RDJC_RESTRICT
            (PAR_SESSION        in  ve_ty_session              ,
             PAR_RESULT         out ve_ty_result               ,
             PAR_EOID_JUNCTION  in  number                     ,
             PAR_EOID_RDEL_OLD  in  number                     ,
             PAR_EOID_RDEL_NEW  in  number                     );

The type in the database is defined as follows:

create or replace type         VE_TY_RESULT

as       object

        (

         RES_TYPE                            number            (0001)   ,

         RES_CODE                            number            (0010)   ,

         RES_MESS                            varchar2          (0240)   ,

         RES_TIME                            date                       ,

         RES_INFO                            ve_ty_info

                                                                        );

From all that I've read so far, I think that OA is capable of what is needed.
Maybe you could direct me to the right part of the documentation.

Thank you.

Best regards,
Holger.

[Ady]

Hello Jupp,

At the moment if you want to work with Oracle UDTs you need to do add additional code manually. It does not work out of the box. I have prepared a sample application that calls a stored procedure with a UDT parameter. This sample is based on the ODP samples that are provided when you install the ODP driver. You can find them under - ORACLE_BASE\ORACLE_HOME\ODP.NET\Samples\UDT directory

In the 'Object1.cs' file you can find the sql for the UDT and stored procedure. Following are the things you need to keep in mind

1. In order to use UDTs from your .NET application you need to implement 2 classes for each UDT - a class that represents the UDT and a factory class that returns instances of this class. You can find more information about this, here.
2. These classes then need to be decorated with attributes defined in the Oracle.DataAccess.dll . You will need to add a reference to this assembly in your project
3. The attached sample has the required implementation. You will need to provide a similar implementation for your UDT
4. You then need to create a partial class for the generated context class and add a method to invoke the stored procedure. Please have a look at the 'ODP_OBJ1_SAMPLE_UPD_CONTACTS' method in the sample.
5. Please note that you need to call context.SaveChanges/ClearChanges after you execute the stored procedure in order to commit/rollback the transaction.

 Do get back in case you need further assistance.

Greetings,
Ady
the Telerik team