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

[Daniel Morgan]

Thanks!

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 ();
			cmd2.CommandText = "SELECT COL1, COUNT(COL2) AS MAX_COL1 FROM MONO_TEST_TABLE3 GROUP BY COL1";
			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] 

Explanation: 

  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,
                                ErrorHandle.Handle,
                                1,
                                2,
                                0); 

                        switch (status) {
                        case OciGlue.OCI_NO_DATA:
                                moreResults = false;
                                break;
                        case OciGlue.OCI_DEFAULT:
                                moreResults = true;
                                break;
                        case OciGlue.OCI_SUCCESS_WITH_INFO:
                                moreResults = true;
                                break;
                        default:
                                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 :) 

regards, 

Joost Evertse 

  

  

2uXf)+2uXX̚&rX̚&r+wjzY