[Mono-list] Class status page for OracleCommandBuilder.DeriveParameters shows it implemented but throws not implemented error

[Daniel Morgan]

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 <prelude20Si at yahoo.com> wrote:

> From: Ivan Lopez <prelude20Si at yahoo.com>
> Subject: [Mono-list] Class status page for OracleCommandBuilder.DeriveParameters shows it implemented but throws not implemented error
> To: mono-list at lists.ximian.com
> 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  -  Mono-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list
>