private StoredProcedure GetStoredProcedureFromPLSQLProcedure(PLSQLProcedure procedure, string packageName)
{
StoredProcedure storedProc = null;
if (procedure != null)
{
// Check if there are any ref cursors in the procedure parameters
if (procedure.IsContainingRefCursor && !procedure.IsFunction)
{
// Create ref cursor stored procedure
RefCurStoredProcedure proc = RefCurStoredProcedure.Create(procedure.Name, packageName, mySchema.ConnectionString);
// Check if procedure is valid
if (proc.Status == RefCurStoredProcedureStatus.Valid)
{
// Convert the RefCurStoredProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure, proc);
}
else
{
string errorText = string.Format("Error when trying to import procedure {0} that has a Ref Cursor parameter!" + Environment.NewLine + Environment.NewLine, proc.Name) +
string.Format("Status: {0}", proc.Status.ToString()) + Environment.NewLine + Environment.NewLine +
proc.ErrorText;
MessageBox.Show(errorText, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
// Convert the PLSQLProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure);
}
}
return(storedProc);
}
public static StoredProcedure CreateStoredProcedure(PLSQLProcedure plsqlProcedure, RefCurStoredProcedure refCurStoredProcedure)
{
StoredProcedure storedProc = null;
if (plsqlProcedure != null &&
plsqlProcedure.Status == ProcedureStatus.Valid &&
plsqlProcedure.Parameters.Count > 0 &&
refCurStoredProcedure != null &&
refCurStoredProcedure.Status == RefCurStoredProcedureStatus.Valid &&
refCurStoredProcedure.ParameterList.Count > 0)
{
// Add procedures found in package specification that was parsed
storedProc = new StoredProcedure();
// Set the name of the procedure
storedProc.ProcedureName = refCurStoredProcedure.Name;
// If returning a ref cursor
storedProc.IsReturningRefCursor = true;
// Get the name of the column that is the Ref Cursor parameter
storedProc.RefCursorParameterName = refCurStoredProcedure.InitialParameterList.Where(p => p.ProviderType == Oracle.DataAccess.Client.OracleDbType.RefCursor).ToList()[0].ColumnName;
// Counter to set sequence for parameters
// First parameter has sequence = 1
int paramSequenceCounter = 1;
foreach (PLSQLProcedureParameter param in plsqlProcedure.Parameters)
{
ProcedureProperty storedProcParam = null;
if (param.ParameterType == ParameterType.DataType &&
param.ParameterTypeDataType_DataType == ParameterDBDataType.Ref_Cur)
{
foreach (OracleDataTypeInfo refCurParam in refCurStoredProcedure.ParameterList)
{
ProcedureProperty newProcParam = new ProcedureProperty();
// Set parameter name
newProcParam.Name = refCurParam.ColumnName;
newProcParam.Length = refCurParam.ColumnSize;
newProcParam.Precision = refCurParam.Precision;
newProcParam.Scale = refCurParam.Scale;
// Set direction of parameter
switch (refCurParam.Direction)
{
case System.Data.ParameterDirection.Input:
newProcParam.PropertyType = DbPropertyType.In;
break;
case System.Data.ParameterDirection.InputOutput:
newProcParam.PropertyType = DbPropertyType.InOut;
break;
case System.Data.ParameterDirection.Output:
newProcParam.PropertyType = DbPropertyType.Out;
break;
default:
newProcParam.PropertyType = DbPropertyType.In;
break;
}
// Sequence of the parameters
newProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
newProcParam.StoredProcedure = storedProc;
// Not supported.
newProcParam.Text = string.Empty;
newProcParam.OriginalTable = string.Empty;
newProcParam.OriginalColumn = string.Empty;
newProcParam.DbDatatype = refCurParam.DataType;
// Not supported with defaultvalues
newProcParam.DefaultValue = string.Empty;
// Set mandatory flag
newProcParam.IsMandatory = false;
// Add the property to the stored procedure
storedProc.Properties.Add(newProcParam);
// Add one to sequence counter
paramSequenceCounter++;
}
}
else
{
storedProcParam = ProcedurePropertyHelper.CreateProcedureProperty(param);
// Sequence of the parameters
storedProcParam.Sequence = paramSequenceCounter;
// Set the parent stored procedure for the parameter
storedProcParam.StoredProcedure = storedProc;
// Add the property to the stored procedure
storedProc.Properties.Add(storedProcParam);
// Add one to sequence counter
paramSequenceCounter++;
}
}
}
return(storedProc);
}
