Oracle stored procedure type and user data type

Question

I have an Oracle stored procedure that takes two parameters: a user data type and a string.

Calling a stored procedure in Oracle, I would do the following:

EXECUTE MY_STORED_PROCEDURE(MYTYPE_T(99, 231), 'mystring') 

How can I accomplish this with C #? I understand that I need to configure the command as a stored procedure, but how can I specify the first parameter as a user data type?

Update:

MYTYPE_T is TABLE OF NUMBER created using

 CREATE OR REPLACE TYPE mytype_t AS TABLE OF NUMBER ; 

Answer 1

You cannot do this easily with the deprecated System.Data.OracleClient but you can use oracle ODP using UDT. If this is not an option, I'm not sure how you can do this using parameters in C # with System.Data.

There are many examples in ODP, and there are examples in the links above.

I am going to add some more links that hopefully help:

• visual studio index ODP
• this shows how exactly to use ODT to create custom classes and name them (do note that this is halfway, they go through the tool to create custom types above it; example is a step-by-step guide that is quite thorough and should be right where you need to be)
• Download : now this guy is also installing sample files, this is another amazing example of what you need to do: after installing goto [installation path to directory] .. \ product \ 11.2.0 \ client_1 \ odp.net \ Samples \ 4 \ UDT \ object1.cs

It actually pays so that the ODT tools for Visual Studio can create your classes for your UDTs for you (like IOracleCustomType, etc.). You can enter them and make changes according to your needs. then, as soon as everything is said and done (fragment from object1.cs):

  Person p1 = new Person(); p1.Name = "John"; p1.Address = "Address1"; p1.Age = 20; // Establish a connection to Oracle OracleConnection con = new OracleConnection(constr); con.Open(); // Update Person object and insert it into a database table OracleCommand cmd = new OracleCommand(sql1, con); cmd.CommandType = CommandType.StoredProcedure; OracleParameter param1 = new OracleParameter(); param1.OracleDbType = OracleDbType.Object; param1.Direction = ParameterDirection.InputOutput; // Note: The UdtTypeName is case-senstive param1.UdtTypeName = "SCOTT.ODP_OBJ1_SAMPLE_PERSON_TYPE"; param1.Value = p1; cmd.Parameters.Add(param1); 

also note that the Person class must implement IOracleCustomType (which can be created by reference in # 2)

 /* Person Class An instance of a Person class represents an ODP_OBJ1_SAMPLE_PERSON_TYPE object A custom type must implement INullable and IOracleCustomType interfaces */ public class Person : INullable, IOracleCustomType 

The above is for the full custom type, but you are after binding the associative ODP array:

http://weblogs.asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx

you want to use

 param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; 

and everything should fall into place

Answer 2

You can pass a custom data type from C # to an Oracle procedure only if that data type is defined by DB. Check out this article to help you get started.