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

[Joost Evertse]

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