[Oracle] Using ODP. NET (ODAC) in ASP. NET to perform operations on stored procedures that contain custom types of data

Create a custom type under the schema (the type under packages is invalid)

1. Create an object type first

Create or replace type PLANTINFO_TYPE AS OBJECT
(
PLANT VARCHAR2 (6 ),
SUBPLANT VARCHAR2 (6)
)

 

2. Create another collections type

 

Create or replace type PLANTINFO_TAB_TYPE as table of PLANTINFO_TYPE;

 

 

3. Create a procedure, whose values reference the above type

 

Code

Create or replace package MYPACK_TEST
PROCEDURE TEST2 (PARAM1 IN MF_SAVEINFO_TAB_TYPE, PARAM2 OUT SYS_REFCURSOR );
END MYPACK_TEST;

Create or replace package body MYPACK_TEST
PROCEDURE TEST2 (PARAM1 IN MF_SAVEINFO_TAB_TYPE, PARAM2 OUT SYS_REFCURSOR) IS
BEGIN
OPEN PARAM2
Select plant, subplant from table (PARAM1 );
END TEST2;
END MYPACK_TEST;

 

 

If you want to use the above procedure in ASP. NET, microsoft oracleclient does not exist, you must reference

ODP. NET, a component in the ODAC suite. Please go to the official Oracle website.

 

The hypothetical environment has been established, so we can see how to use odp.net to use this stored Course

1. Prepare a class for oracle customer types first

 

Code

[OracleCustomTypeMappingAttribute ("PLANTINFO_TYPE")]
Public class PlantInfoFactory: IOracleCustomTypeFactory
{
# Region IOracleCustomTypeFactory Members

Public IOracleCustomType CreateObject ()
{
Return new PlantInfo ();
}

# Endregion
}

[OracleCustomTypeMappingAttribute ("PLANTINFO_TAB_TYPE")]
Public class PlantInfo_TabFactory: imo-learraytypefactory
{
# Region imo-learraytypefactory Members

Public Array CreateArray (int numElems)
{
Return new PlantInfo [numElems];
}

Public Array CreateStatusArray (int numElems)
{
Return null;
}

# Endregion
}

Public class PlantInfo: IOracleCustomType
{
Private bool p_mIsNull;

[OracleObjectMappingAttribute ("PLANT")]
Public String PLANT {get; set ;}

[OracleObjectMappingAttribute ("SUBPLANT")]
Public String SUBPLANT {get; set ;}

Public static PlantInfo Null
{
Get
{
PlantInfo info = new PlantInfo ();
Info. p_mIsNull = true ;;
Return info;
}
}

# Region INullable Members

Public bool IsNull
{
Get {return p_mIsNull ;}
}

# Endregion

# Region IOracleCustomType Members

Public void FromCustomObject (OracleConnection con, IntPtr pUdt)
{
If (PLANT! = Null)
OracleUdt. SetValue (con, pUdt, "PLANT", PLANT );
Else
Throw new NullReferenceException ("SaveInfo. PLANT is null ");

If (SUBPLANT! = Null)
OracleUdt. SetValue (con, pUdt, "SUBPLANT", SUBPLANT );
Else
Throw new NullReferenceException ("SaveInfo. SUBPLANT is null ");

}

Public void ToCustomObject (OracleConnection con, IntPtr pUdt)
{
PLANT = (String) OracleUdt. GetValue (con, pUdt, "PLANT ");
SUBPLANT = (String) OracleUdt. GetValue (con, pUdt, "SUBPLANT ");
}

# Endregion

}

 

2. The procedure method directly uses procedure as follows:

 

Code

String ConStr = @ "Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521 ))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyOracleServer); User Id = root; Password = root ;";
PlantInfo [] records = new PlantInfo [1];
PlantInfo record = new PlantInfo ();
Record. PLANT = "W000 ";
Record. SUBPLANT = "MP ";
Records [0] = record;

// DataOP. Instance. ExecuteCommand ("MYPACK_TEST.TEST2", new object [] {records, null });
Using (OracleConnection oc = new OracleConnection (ConStr ))
{
OracleCommand cmd = oc. CreateCommand ();
Cmd. CommandText = "MYPACK_TEST.TEST2 ";
Cmd. CommandType = CommandType. StoredProcedure;

OracleParameter p1 = new OracleParameter ();
P1.OracleDbType = OracleDbType. Array;
P1.Direction = ParameterDirection. Input;
P1.UdtTypeName = "PLANTINFO_TAB_TYPE ";
P1.Value = records;
Cmd. Parameters. Add (p1 );

OracleParameter p2 = new OracleParameter ();
P2.OracleDbType = OracleDbType. RefCursor;
P2.Direction = ParameterDirection. Output;
Cmd. Parameters. Add (p2 );

Oc. Open ();
Try
{
OracleDataReader reader = cmd. ExecuteReader ();
// Cmd. ExecuteNonQuery ();
DataTable dt = new DataTable ();
Dt. Load (reader );
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
Oc. Close ();
}
}

 

3. The above DataTable dt is the result returned by procedure by the consumer.

So far, ASP. NET memory uses this special memory processing method. the good thing about this method is to package the value into a pair of images for the stored procedure, and then perform other operations such as the stored procedure.