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.