public static RefCurStoredProcedure Create(string storedProcedureName, string packageName, string connectionString)
{
RefCurStoredProcedure storedProc = new RefCurStoredProcedure();
storedProc.Name = storedProcedureName;
storedProc.PackageName = packageName;
// Try to call database to see if we can catch the parameters
OracleSchemaInfo.GetRefCursorTypes(connectionString, storedProc);
return(storedProc);
}
public static void GetRefCursorTypes(string connectionString, RefCurStoredProcedure procedure)
{
if (!String.IsNullOrEmpty(connectionString) && procedure != null)
{
// Fetch the initial stored procedure list first
FetchInitialStoredProcedureParameterList(connectionString, procedure);
// Clear the "real" parameterlist
procedure.ParameterList.Clear();
try
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
// Set TestRefCursor
SetTestRefCursor(conn);
OracleCommand cmd = new OracleCommand(procedure.PackageAndProcName, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleDataTypeInfo param in procedure.InitialParameterList)
{
if (param.Direction == System.Data.ParameterDirection.ReturnValue)
{
cmd.Parameters.Add(param.ColumnName, (OracleDbType)param.ProviderType, System.Data.ParameterDirection.ReturnValue);
}
else
{
cmd.Parameters.Add(param.ColumnName, (OracleDbType)param.ProviderType, param.ColumnSize ?? 0, DBNull.Value, param.Direction);
}
}
using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
// Read the SchemaTable for the datareader
DataTable dt = reader.GetSchemaTable();
for (int i = 0; i < reader.FieldCount; i++)
{
OracleDataTypeInfo dti = new OracleDataTypeInfo();
int ordinal;
// ProviderType
ordinal = dt.Columns["ProviderType"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.ProviderType = null;
}
else
{
dti.ProviderType = ((OracleDbType)dt.Rows[i].ItemArray[ordinal]);
}
// Direction
dti.Direction = System.Data.ParameterDirection.Output;
// Datatype
dti.DataType = ConvertProviderType(dti.ProviderType);
// ColumnName
ordinal = dt.Columns["ColumnName"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.ColumnName = String.Empty;
}
else
{
dti.ColumnName = (string)dt.Rows[i].ItemArray[ordinal];
}
// ColumnSize
ordinal = dt.Columns["ColumnSize"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.ColumnSize = null;
}
else
{
dti.ColumnSize = (int)dt.Rows[i].ItemArray[ordinal];
}
// IsByteSemantic
ordinal = dt.Columns["IsByteSemantic"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.IsByteSematic = null;
}
else
{
dti.IsByteSematic = (bool)dt.Rows[i].ItemArray[ordinal];
}
// NumericPrecision
ordinal = dt.Columns["NumericPrecision"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.Precision = null;
}
else
{
dti.Precision = (short)dt.Rows[i].ItemArray[ordinal];
}
// NumericScale
ordinal = dt.Columns["NumericScale"].Ordinal;
if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value)
{
dti.Scale = null;
}
else
{
dti.Scale = (short)dt.Rows[i].ItemArray[ordinal];
}
// Add to list
procedure.ParameterList.Add(dti);
}
}
// Reset TestRefCursor
if (!ResetTestRefCursor(conn))
{
procedure.Status = RefCurStoredProcedureStatus.NoRefCursorTestSectionDefined;
procedure.ErrorText = string.Format("The stored procedure is missing a test section for returning the structure of the ref cursor.\n" +
"Check the instructions for how to define this section.");
}
// Test if any ref cursor parameters (metadata) was found
else if (procedure.ParameterList.Count == 0)
{
procedure.Status = RefCurStoredProcedureStatus.NoRefCursorMetadataFound;
procedure.ErrorText = string.Format("No Metadata could be fetched from the Stored Procedure for the Ref Cursor.\n" +
"This is probably because the test section of the stored procedure for returning\n" +
"the structure of the ref cursor doesn't return any ref cursor. Check instructions\n" +
"for how to define the test section.");
}
conn.Close();
}
}
catch (OracleException e)
{
procedure.Status = RefCurStoredProcedureStatus.OracleError;
procedure.ErrorText = e.ToString();
}
catch (Exception e)
{
procedure.Status = RefCurStoredProcedureStatus.OtherError;
procedure.ErrorText = e.ToString();
}
}
}
private static void FetchInitialStoredProcedureParameterList(string connectionString, RefCurStoredProcedure procedure)
{
if (!String.IsNullOrEmpty(connectionString) && procedure != null)
{
// Clear list
procedure.InitialParameterList.Clear();
if (procedure.Status == RefCurStoredProcedureStatus.Valid)
{
try
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
string sqlText = string.Format("select SEQUENCE" +
" ,ARGUMENT_NAME" +
" ,DATA_TYPE" +
" ,IN_OUT" +
" ,DATA_LENGTH" +
" ,DATA_PRECISION" +
" ,DATA_SCALE" +
" from USER_ARGUMENTS" +
" where OBJECT_NAME = '{0}'" +
" and PACKAGE_NAME = '{1}'" +
" and DATA_LEVEL = 0" +
" order by SEQUENCE",
procedure.Name.ToUpper(),
procedure.PackageName.ToUpper());
OracleCommand cmd = new OracleCommand(sqlText, conn);
bool firstParam = true;
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
OracleDataTypeInfo param = new OracleDataTypeInfo();
param.ColumnName = reader["ARGUMENT_NAME"].ToString();
param.ProviderType = GetOracleDbType(reader["DATA_TYPE"].ToString());
// Check if parameter is not supported (null)
if (param.ProviderType == null)
{
procedure.Status = RefCurStoredProcedureStatus.ParameterDataTypeNotSupported;
procedure.ErrorText = string.Format("The stored procedure contains a parameter with an unsupported data type ({0})!", reader["DATA_TYPE"].ToString());
reader.Close();
break;
}
param.Direction = GetDirection(reader["IN_OUT"].ToString());
if (reader["DATA_LENGTH"] != DBNull.Value)
{
param.ColumnSize = int.Parse(reader["DATA_LENGTH"].ToString());
}
else
{
param.ColumnSize = null;
}
if (reader["DATA_PRECISION"] != DBNull.Value)
{
param.Precision = short.Parse(reader["DATA_PRECISION"].ToString());
}
else
{
param.Precision = null;
}
if (reader["DATA_SCALE"] != DBNull.Value)
{
param.Scale = short.Parse(reader["DATA_SCALE"].ToString());
}
else
{
param.Scale = null;
}
procedure.InitialParameterList.Add(param);
// Check if return value for a function
if (firstParam && string.IsNullOrEmpty(param.ColumnName) && param.Direction == System.Data.ParameterDirection.Output)
{
param.Direction = System.Data.ParameterDirection.ReturnValue;
procedure.IsFunction = true;
}
firstParam = false;
}
}
conn.Close();
}
}
catch (OracleException e)
{
procedure.Status = RefCurStoredProcedureStatus.OracleError;
procedure.ErrorText = e.ToString();
}
catch (Exception e)
{
procedure.Status = RefCurStoredProcedureStatus.OtherError;
procedure.ErrorText = e.ToString();
}
}
}
}
