Oracle user defined types (UDT) in stored procedures

Thread is closed for posting
2 posts, 0 answers
  1. Jupp
    Jupp avatar
    1 posts
    Member since:
    Feb 2013

    Posted 15 Feb 2013 Link to this post

    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, 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,
  2. Ady
    Ady avatar
    589 posts

    Posted 20 Feb 2013 Link to this post

    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.

    the Telerik team
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
Back to Top