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