// get arguments for stand-alone stored procedures/functions private void GetProcedureArguments(MetaProcedure proc) { if (con.State != ConnectionState.Open) { con.Open(); } // GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " + " POSITION, SEQUENCE, "+ " IN_OUT AS DIRECTION, "+ " DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+ "FROM SYS.ALL_ARGUMENTS " + "WHERE OWNER = '" + proc.Owner + "' " + "AND OBJECT_NAME = '" + proc.Name + "' " + "AND PACKAGE_NAME IS NULL " + "AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " + "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL"; IDbCommand cmd = con.CreateCommand(); cmd.CommandText = sql; IDataReader reader = cmd.ExecuteReader(); string procType = "Procedures"; while (reader.Read()) { string procName = reader.GetString(0); string argName = reader.GetString(2); if (argName.Equals("(RETURN)")) { procType = "Functions"; } else { procType = "Procedures"; } string direction = reader.GetString(5); if (argName.Equals("(RETURN)")) { direction = String.Empty; } string dataType = reader.GetString(6); MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType, argName, direction, dataType); proc.Arguments.Add(arg); } reader.Close(); reader = null; }
public MetaProcedureArgument this[string name] { get { MetaProcedureArgument p = null; foreach (object o in list) { p = (MetaProcedureArgument)o; if (p.ArgumentName.Equals(name)) { return(p); } } throw new Exception("MetaProcedureArgument not found"); } }
// get arguments for stand-alone stored procedures/functions private void GetProcedureArguments (MetaProcedure proc) { if (con.State != ConnectionState.Open) con.Open(); // GET ARGUMENTS FOR STAND-ALONE PROCEDURES/FUNCTIONS string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " + " POSITION, SEQUENCE, " + " IN_OUT AS DIRECTION, " + " DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " + "FROM SYS.ALL_ARGUMENTS " + "WHERE OWNER = '" + proc.Owner + "' " + "AND OBJECT_NAME = '" + proc.Name + "' " + "AND PACKAGE_NAME IS NULL " + "AND DATA_LEVEL = 0 AND DATA_TYPE IS NOT NULL " + "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL"; IDbCommand cmd = con.CreateCommand (); cmd.CommandText = sql; IDataReader reader = cmd.ExecuteReader (); string procType = "Procedures"; while (reader.Read ()) { string procName = reader.GetString (0); string argName = reader.GetString (2); if (argName.Equals ("(RETURN)")) procType = "Functions"; else procType = "Procedures"; string direction = reader.GetString (5); if (argName.Equals ("(RETURN)")) direction = String.Empty; string dataType = reader.GetString (6); MetaProcedureArgument arg = new MetaProcedureArgument(proc.Owner, procName, procType, argName, direction, dataType); proc.Arguments.Add (arg); } reader.Close (); reader = null; }
// get procedures/functions for a given stored package and get the // arguments for each procedure/function private void GetPackageProcedures (MetaProcedure pkg) { if (con.State != ConnectionState.Open) con.Open(); // GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " + " POSITION, SEQUENCE, " + " IN_OUT AS DIRECTION, " + " DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE " + "FROM SYS.ALL_ARGUMENTS " + "WHERE OWNER = '" + pkg.Owner + "' " + "AND PACKAGE_NAME = '" + pkg.Name + "' " + "AND DATA_LEVEL = 0 " + "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL"; IDbCommand cmd = con.CreateCommand (); cmd.CommandText = sql; IDataReader reader = cmd.ExecuteReader (); // Notes: // 1. an Oracle stored package can overloaded functions or procedures // 2. stand-alone functions or procedures can not be overloaded // 3. a procedure with no arguments will still have one row - data_type will be null string previousProcName = "~"; string previousOverload = "~"; MetaProcedure proc = null; string procType = "Procedures"; while (reader.Read ()) { string procName = reader.GetString (0); string argName = reader.GetString (2); string overload = String.Empty; if (!reader.IsDBNull (1)) overload = reader.GetString (1); string direction = String.Empty; if (!reader.IsDBNull (5)) direction = reader.GetString (5); string dataType = String.Empty; if (!reader.IsDBNull (6)) dataType = reader.GetString (6); if (!procName.Equals (previousProcName) || !previousOverload.Equals (overload)) { if (argName.Equals ("(RETURN)") && (!dataType.Equals (String.Empty))) { procType = "Functions"; direction = String.Empty; } else procType = "Procedures"; proc = new MetaProcedure (String.Empty, procName, procType); pkg.Procedures.Add (proc); previousProcName = procName; previousOverload = overload; } if (!dataType.Equals (String.Empty)) { MetaProcedureArgument arg = new MetaProcedureArgument (pkg.Owner, procName, procType, argName, direction, dataType); proc.Arguments.Add (arg); } } reader.Close (); reader = null; }
// get procedures/functions for a given stored package and get the // arguments for each procedure/function private void GetPackageProcedures(MetaProcedure pkg) { if (con.State != ConnectionState.Open) { con.Open(); } // GET ARGUMENTS FOR PROCEDURES/FUNCTIONS FOR PACKAGES string sql = "SELECT OBJECT_NAME, OVERLOAD, NVL(ARGUMENT_NAME,'(RETURN)') AS ARGUMENT_NAME, " + " POSITION, SEQUENCE, "+ " IN_OUT AS DIRECTION, "+ " DECODE(TYPE_NAME, NULL, DATA_TYPE, TYPE_OWNER || '.' || TYPE_NAME) AS DATA_TYPE "+ "FROM SYS.ALL_ARGUMENTS " + "WHERE OWNER = '" + pkg.Owner + "' " + "AND PACKAGE_NAME = '" + pkg.Name + "' " + "AND DATA_LEVEL = 0 " + "ORDER BY OBJECT_NAME, OVERLOAD, POSITION, SEQUENCE, DATA_LEVEL"; IDbCommand cmd = con.CreateCommand(); cmd.CommandText = sql; IDataReader reader = cmd.ExecuteReader(); // Notes: // 1. an Oracle stored package can overloaded functions or procedures // 2. stand-alone functions or procedures can not be overloaded // 3. a procedure with no arguments will still have one row - data_type will be null string previousProcName = "~"; string previousOverload = "~"; MetaProcedure proc = null; string procType = "Procedures"; while (reader.Read()) { string procName = reader.GetString(0); string argName = reader.GetString(2); string overload = String.Empty; if (!reader.IsDBNull(1)) { overload = reader.GetString(1); } string direction = String.Empty; if (!reader.IsDBNull(5)) { direction = reader.GetString(5); } string dataType = String.Empty; if (!reader.IsDBNull(6)) { dataType = reader.GetString(6); } if (!procName.Equals(previousProcName) || !previousOverload.Equals(overload)) { if (argName.Equals("(RETURN)") && (!dataType.Equals(String.Empty))) { procType = "Functions"; direction = String.Empty; } else { procType = "Procedures"; } proc = new MetaProcedure(String.Empty, procName, procType); pkg.Procedures.Add(proc); previousProcName = procName; previousOverload = overload; } if (!dataType.Equals(String.Empty)) { MetaProcedureArgument arg = new MetaProcedureArgument(pkg.Owner, procName, procType, argName, direction, dataType); proc.Arguments.Add(arg); } } reader.Close(); reader = null; }