Output parameter for TABLE OF VARCHAR2 Oracle odp.net

Question

Hi all,

I have this type:

TYPE tipo_TableVarchar2 IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;

and procedure procedure get_array (p_arr out TableVarchar2 ) is ...

OracleParameter param = new OracleParameter(); param.OracleDbType = OracleDbType.Varchar2; param.CollectionType = OracleCollectionType.PLSQLAssociativeArray; param.ParameterDirection = ParameterDirection.Output;

cm.Parameters.Add(param);

How can use Oracle Parameter Output for get the value ??

Thanks in advanced.

Answer 1

Look into the Oracle 11g odp.net drivers, which allows you to pass Oracle Types. There are some samples on Oracle's site, as well as in the install directory of your 11g home... C:\Oracle\Ora11g_Home\odp.net\samples\2.x\UDT

I had to pull back an object type in this sample.

You'll have something like this...

        OracleParameter p_params = new OracleParameter();
        p_params.ParameterName = "params";
        p_params.OracleDbType = OracleDbType.Object;
        p_params.Direction = ParameterDirection.InputOutput;
        // Note: The UdtTypeName is case-senstive
        p_params.UdtTypeName = "SCHEMA_NAME.TYPE_NAME";

You also need a class that defines your type. Do some searches for "OracleObjectMappingAttribute" and "IOracleCustomTypeFactory".

Side note - I am using the 11g odp.net drivers to hit a 10g database. You just need to reference the following.dlls in your project: oci.dll, oraociicus11.dll, and OraOps11w.dll. You should already be referencing Oracle.DataAccess.dll (Just make sure you start hitting the 11g version).