defined type to a Oracle stored procedure
With reference to Oracle: Variable number of parameters to a stored procedure
I have s stored procedure to insert multiple Users into a User table. The table is defined like:
CREATE TABLE "USER"
(
"Name" VARCHAR2(50),
"Surname" VARCHAR2(50),
"Dt_Birth" DATE,
)
The stored procedure to insert multiple Users is:
type userType is record (
name varchar2(100),
...
);
type userList is table of userType index by binary_integer;
procedure array_insert (p_userList in userList) is
begin
forall i in p_userList.first..p_userList.last
insert into users (username) values (p_userList(i) );
end array_insert;
How can I call the stored procedure from C# passing a userList of userType? Thanks
Here is a helper I used to work with stored procedures in Oracle:
internal class OracleDataHelper
{
#region Variables
private static readonly string _connectionString;
#endregion
#region Constructors
static OracleDataHelper()
{
//_connectionString = ConfigurationManager.ConnectionStrings["..."]
// .ConnectionString;
}
#endregion
public static object ExecuteScalar(string query)
{
object result;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
result = command.ExecuteScalar();
command.Dispose();
conn.Close();
}
return result;
}
public static object ExecuteScalar(
string query,
params object[] parameters)
{
object result;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
command.Parameters.AddRange(
ConvertParameters(parameters)
);
result = command.ExecuteScalar();
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query, new List<OracleParameter>());
}
public static int ExecuteNonQuery(
string query,
List<OracleParameter> parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.CommandType = CommandType.Text;
command.BindByName = true;
command.Parameters.AddRange(
ConvertParameters(parameters.ToArray())
);
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteNonQuery(
string query,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(query, conn);
command.BindByName = true;
command.CommandType = CommandType.Text;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static OracleDataReader ExecuteReader(
OracleConnection conn,
string commandText
)
{
OracleCommand command = new OracleCommand(commandText, conn);
return command.ExecuteReader();
}
public static IDataReader ExecuteReader(
OracleConnection conn,
string spName,
out List<OracleParameter> outParameters,
params object[] parameters)
{
throw new NotImplementedException();
}
public static int ExecuteProcedure(
string spName,
out OutputParameters outputParameters,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
outputParameters = GetOutputParameters(command.Parameters);
command.Dispose();
conn.Close();
}
return result;
}
public static int ExecuteProcedure(
string spName,
params object[] parameters)
{
int result = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
result = command.ExecuteNonQuery();
command.Dispose();
conn.Close();
}
return result;
}
public static OracleDataReader ExecuteProcedure(
OracleConnection conn,
string spName,
out OutputParameters outputParameters,
params object[] parameters
)
{
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
OracleDataReader reader = command.ExecuteReader();
outputParameters = GetOutputParameters(command.Parameters);
command.Dispose();
return reader;
}
public static OracleDataReader ExecuteProcedure(
OracleConnection conn,
string spName,
params object[] parameters
)
{
OracleCommand command = new OracleCommand(spName, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(ConvertParameters(parameters));
OracleDataReader reader = command.ExecuteReader();
command.Dispose();
return reader;
}
private static OracleParameter[] ConvertParameters(object[] parameters)
{
parameters = parameters ?? new object[] { };
int parametersCount = parameters.Length;
OracleParameter[] parametersArray =
new OracleParameter[parametersCount];
for (int i = 0; i < parametersCount; i++)
{
object parameter = parameters[i];
OracleParameter oracleParameter;
if (parameter is OracleParameter)
{
oracleParameter = (OracleParameter)parameter;
if (null == oracleParameter.Value)
{
oracleParameter.Value = DBNull.Value;
}
}
else
{
oracleParameter = new OracleParameter();
oracleParameter.Value = parameter == null ?
DBNull.Value :
parameter;
}
// adding udt mapping for the parameter
if (oracleParameter.Value != null &&
oracleParameter.Value is IOracleCustomTypeFactory)
{
MemberInfo info = oracleParameter.Value.GetType();
OracleCustomTypeMappingAttribute[] attributes =
info.GetCustomAttributes(
typeof(OracleCustomTypeMappingAttribute),
false
) as OracleCustomTypeMappingAttribute[];
if (null != attributes && attributes.Length > 0)
{
oracleParameter.UdtTypeName = attributes[0].UdtTypeName;
}
}
parametersArray[i] = oracleParameter;
}
return parametersArray;
}
private static OutputParameters GetOutputParameters(
OracleParameterCollection parameters)
{
OutputParameters outputParameters = new OutputParameters();
foreach (OracleParameter parameter in parameters)
{
if (parameter.Direction == ParameterDirection.Output)
outputParameters.Add(parameter);
}
return outputParameters;
}
internal static string ConnectionString
{
get { return _connectionString; }
}
}
These methods work with UDT as well as they work with simple parameters.
Here is an example of UDT entity:
[Serializable]
[OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
public class SdoGeometry : IOracleCustomTypeFactory,
IOracleCustomType,
ICloneable, INullable
{
#region Variables
private int _sdoGType;
private int _sdoSrid;
private SdoPoint _sdoPoint;
private SdoElemInfo _sdoElemInfo;
private SdoOrdinates _sdoOrdinate;
private bool _sdoGTypeIsNull;
private bool _sdoSridIsNull;
#endregion
#region Properties
[OracleObjectMappingAttribute("SDO_GTYPE")]
public int SdoGType
{
get { return _sdoGType; }
set
{
_sdoGType = value;
_sdoGTypeIsNull = false;
}
}
public SdoGeometryType SdoGeometryType
{
get { return (Entities.Geometry.SdoGeometryType)(SdoGType % 100); }
}
public int Dimensions
{
get { return (int)(SdoGType / 1000); }
}
public int LrsDimensions
{
get { return (int)((SdoGType / 100) % 10); }
}
[OracleObjectMappingAttribute("SDO_SRID")]
public int SdoSrid
{
get { return _sdoSrid; }
set
{
_sdoSrid = value;
_sdoSridIsNull = false;
}
}
[OracleObjectMappingAttribute("SDO_POINT")]
public SdoPoint SdoPoint
{
get { return _sdoPoint; }
set { _sdoPoint = value; }
}
[OracleObjectMappingAttribute("SDO_ELEM_INFO")]
public SdoElemInfo SdoElemInfo
{
get { return _sdoElemInfo; }
set { _sdoElemInfo = value; }
}
[OracleObjectMappingAttribute("SDO_ORDINATES")]
public SdoOrdinates SdoOrdinates
{
get { return _sdoOrdinate; }
set { _sdoOrdinate = value; }
}
public static SdoGeometry Null
{
get
{
SdoGeometry obj = new SdoGeometry();
return obj;
}
}
#endregion
#region Constructors
public SdoGeometry()
{
_sdoGTypeIsNull = true;
_sdoSridIsNull = true;
_sdoElemInfo = SdoElemInfo.Null;
_sdoOrdinate = SdoOrdinates.Null;
_sdoPoint = SdoPoint.Null;
}
public SdoGeometry(SdoGeometry obj)
{
if (obj != null && this != obj)
{
SdoGType = obj.SdoGType;
SdoSrid = obj.SdoSrid;
SdoPoint = (SdoPoint)obj.SdoPoint.Clone();
SdoElemInfo = (SdoElemInfo)obj.SdoElemInfo.Clone();
SdoOrdinates = (SdoOrdinates)obj.SdoOrdinates.Clone();
}
}
public SdoGeometry(
int gType,
int srid,
SdoPoint point,
SdoElemInfo elemInfo,
SdoOrdinates ordinate)
{
SdoGType = gType;
SdoSrid = srid;
SdoPoint = (SdoPoint)point.Clone();
SdoElemInfo = (SdoElemInfo)elemInfo.Clone();
SdoOrdinates = (SdoOrdinates)ordinate.Clone();
}
#endregion
#region ICloneable Members
public object Clone()
{
return new SdoGeometry(this);
}
#endregion
#region IOracleCustomType Members
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
if (!_sdoGTypeIsNull)
OracleUdt.SetValue(con, pUdt, "SDO_GTYPE", SdoGType);
if (!SdoOrdinates.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_ORDINATES", SdoOrdinates);
if (!SdoElemInfo.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_ELEM_INFO", SdoElemInfo);
if (!_sdoSridIsNull)
OracleUdt.SetValue(con, pUdt, "SDO_SRID", SdoSrid);
else
OracleUdt.SetValue(con, pUdt, "SDO_SRID", DBNull.Value);
if (!SdoPoint.IsNull)
OracleUdt.SetValue(con, pUdt, "SDO_POINT", SdoPoint);
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object sdoGType = OracleUdt.GetValue(con, pUdt, "SDO_GTYPE");
_sdoGTypeIsNull = sdoGType == null || sdoGType is DBNull;
if (!_sdoGTypeIsNull)
SdoGType = (int)sdoGType;
SdoOrdinates =
(SdoOrdinates)OracleUdt.GetValue(con, pUdt, "SDO_ORDINATES");
SdoElemInfo =
(SdoElemInfo)OracleUdt.GetValue(con, pUdt, "SDO_ELEM_INFO");
object sdoSrid = OracleUdt.GetValue(con, pUdt, "SDO_SRID");
if (!(sdoSrid == null || sdoSrid is DBNull))
SdoSrid = (int)sdoSrid;
SdoPoint = (SdoPoint)OracleUdt.GetValue(con, pUdt, "SDO_POINT");
}
#endregion
#region INullable Members
public bool IsNull
{
get { return _sdoGTypeIsNull; }
}
#endregion
#region IOracleCustomTypeFactory Members
public IOracleCustomType CreateObject()
{
return new SdoGeometry();
}
#endregion
}
PS During my project Oracle released 3 versions of ODP.NET. The interesting thing: the code which worked for 2.111.6.10 version of Oracle.DataAcess.dll didn't work for 2.111.6.20 at all!
Don't know which version of ODP.NET is actual now but the samples I posted above works well with 2.111.6.10.
Hope this helps. Good luck!
After many false starts, this post here saved my bacon (binding to a UDT of TABLE OF VARCHAR2(100) ).
Salient points
varchar2(100) ) IOracleCustomType and INullable interfaces. string[] ), and the property must be marked with the OracleArrayMapping attribute. IOracleArrayTypeFactory , IOracleCustomTypeFactory interfaces. It needs the following methods OracleCustomTypeMapping("SCHEMA.UDT_TYPE") where SCHEMA.UDT_TYPE matches your UDT type, viz CREATE TYPE SCHEMA.UDT_TYPE AS TABLE OF VARCHAR2(100) By comparison, the bind on the parameter is straightforward:
var oracleArray = new MyArrayStorageClass
{
Array = new string[] {"Hello", "World"}
};
command.CommandType = CommandType.StoredProcedure;
var param = new OracleParameter("ip_parameterName", OracleDbType.Array)
{
// Case sensitive match to the `OracleCustomTypeMapping` on the factory
UdtTypeName = "SCHEMA.UDT_TYPE",
Value = oracleArray,
Direction = ParameterDirection.Input,
};
command.Parameters.Add(param);
ODP.net supports user defined types. http://www.oracle.com/technology/tech/windows/odpnet/index.html
Google for examples or read the manual.
链接地址: http://www.djcxy.com/p/20286.html下一篇: 定义类型到Oracle存储过程