[Mono-devel-list] OCI_SUCCES_WITH_INFO with System.Data.OracleClient

Daniel Morgan danielmorgan at verizon.net
Sun Jan 9 22:58:35 EST 2005


I have committed your patch into svn trunk HEAD.

I created a test case and ran it on .NET 1.1 and Mono 1.1.3 (with latest svn changes for OracleClient).
I had also added the ability for raising an InfoMessage event on the OracleConnection for warnings.  However, your test case did not produce an InfoMessage on .NET 1.1.  So, this baffles me.  

Here is the test case I created that can be found in TestOracleClient.cs

		static void NullAggregateTest (OracleConnection con)
			Console.WriteLine("  Drop table MONO_TEST_TABLE3...");
			OracleCommand cmd2 = con.CreateCommand ();

			try {
				cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE3";
				cmd2.ExecuteNonQuery ();
			catch(OracleException oe1) {
				// ignore if table already exists

			Console.WriteLine("  Create table MONO_TEST_TABLE3...");
			cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE3 (" +
				" COL1 VARCHAR2(8), "+
				" COL2 VARCHAR2(32))";

			cmd2.ExecuteNonQuery ();

			Console.WriteLine("  Insert some rows into table MONO_TEST_TABLE3...");
			cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','one')";
			cmd2.ExecuteNonQuery ();

			cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','uno')";
			cmd2.ExecuteNonQuery ();
			cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','three')";
			cmd2.ExecuteNonQuery ();
			cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3', null)";
			cmd2.ExecuteNonQuery ();

			cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','few')";
			cmd2.ExecuteNonQuery ();
			OracleDataReader reader = cmd2.ExecuteReader ();
			Console.WriteLine (" Read...");
			while (reader.Read ()) {

				object obj0 = reader.GetValue (0);
				Console.WriteLine("Value 0: " + obj0.ToString ());
				object obj1 = reader.GetValue (1);
				Console.WriteLine("Value 1: " + obj1.ToString ());
				Console.WriteLine (" Read...");

			Console.WriteLine (" No more records.");

-----Original Message-----
From: mono-devel-list-admin at lists.ximian.com [mailto:mono-devel-list-admin at lists.ximian.com] On Behalf Of Joost Evertse
Sent: Sunday, January 09, 2005 4:36 PM
To: mono-devel-list at lists.ximian.com
Subject: [Mono-devel-list] OCI_SUCCES_WITH_INFO with System.Data.OracleClient

When I used this query (entry_text is a CLOB column) in a webpage with System.Data.OracleClient i receveid an ORA-24347 Exception during Fetch() String sql = ( " select c.entry_title, d.entry_text,c.entry_created_on,c.entry_id,c.comment_count " +

" from " + 

" (select * from " + 

" (SELECT a.entry_title,a.entry_created_on, a.entry_id, count(b.COMMENT_ENTRY_ID) comment_count " +

" FROM mt.mt_entry a,mt.mt_comments b " +

" where a.entry_author_id = 1 " +

" and a.ENTRY_ID= b.COMMENT_ENTRY_ID(+) " +

" group by a.entry_title,a.entry_created_on, a.entry_id " +

" order by entry_created_on desc, entry_id desc) " +

" where rownum <11 ) c, " +

" mt.mt_entry d " +

" where c.entry_id = d.entry_id " + 

" order by entry_created_on desc, entry_id desc ");

I looked it up on Oracle Metalink and found this:
Error: ORA-24347 
Text: Warning of a NULL column in an aggregate function  
Cause: A null column was processed by an aggregate function  
Action: An OCI_SUCCESS_WITH_INFO is returned.
*** Important: The notes below are for experienced users - See [NOTE:22080.1] 


  This error is introduced in Oracle8i and is a warning. If an OCI 

  client executes a statement in which an AGGREGATE function  

  processes a NULL value then a warning is signalled to indicate  

  this. The OCI function will return OCI_SUCCESS_WITH_INFO. In  

  many cases the client does not care about this and so should 

  ignore this particular warning and continue as normal. 

  If upgrading from OCI 8.0 to 8.1 you may need to modify the OCI code 

  to cope with this new warning if the code treats OCI_SUCCESS_WITH_INFO 

  as a failure scenario. 



I looked in the System.Data.OracleClient code and found that adding this in OciStatementHandle.cs made my code work: 

  public bool Fetch ()
                        int status = 0; 

                        if (this.disposed) ^M
                                throw new InvalidOperationException ("StatementHandle is already disposed.");

                        status = OciCalls.OCIStmtFetch (Handle,

                        switch (status) {
                        case OciGlue.OCI_NO_DATA:
                                moreResults = false;
                        case OciGlue.OCI_DEFAULT:
                                moreResults = true;
                        case OciGlue.OCI_SUCCESS_WITH_INFO:
                                moreResults = true;
                                OciErrorInfo info = ErrorHandle.HandleError ();
                                throw new OracleException (info.ErrorCode, info.ErrorMessage);

                        return moreResults;

I guess it's not pretty, but if it's just a warning condition, i thought, just add it, continue as normal :) 


Joost Evertse 




More information about the Mono-devel-list mailing list