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); }