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