[Mono-devel-list] OCI_SUCCES_WITH_INFO with System.Data.OracleClient
Joost Evertse
j.evertse at cloudseven.nl
Mon Jan 10 17:27:27 EST 2005
Daniel,
Running the piece of code you send below on my machine (before the patch)
[root at smp Test]# mono test.exe
Drop table MONO_TEST_TABLE3...
Create table MONO_TEST_TABLE3...
Insert some rows into table MONO_TEST_TABLE3...
Read...
Unhandled Exception: System.Data.OracleClient.OracleException:
ORA-24347: Warning of a NULL column in an aggregate function
in <0x00127> System.Data.OracleClient.Oci.OciStatementHandle:Fetch ()
in <0x0001a> System.Data.OracleClient.OracleDataReader:Read ()
in <0x0005d> (wrapper remoting-invoke-with-check)
System.Data.OracleClient.OracleDataReader:Read ()
in <0x00266> TestClob:NullAggregateTest
(System.Data.OracleClient.OracleConnection)
in <0x00041> TestClob:Main (string[])
and after the patch:
[root at smp Test]# mono test.exe
Drop table MONO_TEST_TABLE3...
Create table MONO_TEST_TABLE3...
Insert some rows into table MONO_TEST_TABLE3...
Read...
Value 0: 1
Value 1: 2
Read...
Value 0: 3
Value 1: 2
Read...
No more records.
Some more information:
Oracle version: Oracle8i Enterprise Edition Release 8.1.7.0.0
Client: Release 8.1.7.0.0
Mono runtime :version 1.0.5
Mono C# compiler version 1.0.5.0
Daniel Morgan wrote:
>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
>
>------------------------------------------------------------
>Mail was checked for spam by the Freeware Edition of No Spam Today!
>The Freeware Edition is free for personal and non-commercial use.
>You can remove this notice by purchasing a full license! To order
>or to find out more please visit: http://www.no-spam-today.com
>
>
More information about the Mono-devel-list
mailing list