I would imagine you would look up view SYS.ALL_ARGUMENTS to get the arguments
on a stored procedure. You would have to translate the oracle type string to
an OracleType .net type.
Here is sample code to look up ALL_ARGUMENTS.
// 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 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;
}
--- On Tue, 5/4/10, Ivan Lopez <[email protected]> wrote:
> From: Ivan Lopez <[email protected]>
> Subject: [Mono-list] Class status page for
> OracleCommandBuilder.DeriveParameters shows it implemented but throws not
> implemented error
> To: [email protected]
> Date: Tuesday, May 4, 2010, 7:51 PM
>
> For anyone who can shed some light on this....
>
> I am trying to migrate an application that successfully
> runs under MS.Net
> 2.0 over to Mono. The underlying aspects of the code
> connect to an Oracle
> database.
>
> I noticed on the class status page for the OracleClient
> library that the
> OracleCommandBuilder.DeriveParameters static method
> reflects a status of
> implemented. However, in my debugging and testing I keep
> receiving the "Not
> Implemented" exception.
>
> I decided to dig a little deeper and noticed that the code
> for the
> OracleCommandBuilder.DeriveParameters calls into the
> OracleCommand.DeriveParameters method. This internal
> method, however, throws
> the "Not Implemented" exception.
>
> Is there any work being done on this method or is the
> DeriveParameters
> method a no-go?
>
> ***below is from the mono source files that I looked at
> from SVN
>
> //from the OracleCommandBuilder.cs file
> public static void DeriveParameters (OracleCommand
> command)
> {
> command.DeriveParameters ();
> }
>
> //from the OracleCommand.cs file
> internal void DeriveParameters ()
> {
> if (commandType !=
> CommandType.StoredProcedure)
> throw new InvalidOperationException
> (String.Format ("OracleCommandBuilder
> DeriveParameters only supports CommandType.StoredProcedure,
> not
> CommandType.{0}", commandType));
>
> //OracleParameterCollection
> localParameters = new
> OracleParameterCollection (this);
>
> throw new NotImplementedException ();
> }
>
>
>
> Any insights, recommendations, or ideas would be great.
>
> Thanks,
> Ivan
> --
> View this message in context:
> http://mono.1490590.n4.nabble.com/Class-status-page-for-OracleCommandBuilder-DeriveParameters-shows-it-implemented-but-throws-not-implr-tp2130242p2130242.html
> Sent from the Mono - General mailing list archive at
> Nabble.com.
> _______________________________________________
> Mono-list maillist - [email protected]
> http://lists.ximian.com/mailman/listinfo/mono-list
>
_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
