Forum OpenACS Q&A: Re: Selecting an empty (null) oracle-clob crashes AOLserver on Linux

This is a bug in the Oracle client library (OCI) and is known to Oracle.  We ran across it at Greenpeace in June, 2002 and Oracle confirmed that it is a bug that crops up when you run a client application on X86 Linux that talks to an Oracle server on Solaris.  We were not the first to report it and it fails with other clients, not just the db driver used by AOLserver.

However, what we were seeing was Oracle itself crashing on Solaris ... rather than the client.

You say the only error you get is in the DB log, which logs a core dump on the Solaris side.  Isn't this an indication that your Oracle server on Solaris is crashing, too?  Not the client in Linux (AOLserver)?  My guess is that this is the case and that you've got scripts set up to restart Oracle if it dies.

Our workaround was to set CLOBs to the empty string rather than empty_clob().

Supposedly this was being fixed in OCI - surely by 9i it must be fixed?

As to why SQL*Plus doesn't crash ... Oracle didn't say but clearly it's not using the same OCI library the rest of us use.

Just so you know the latest version of Oracle that we tried prevented the AOLserver crash but still gives errors on Oracle side.

Also we learned that updating the empty clobs with *NULL* apparently solves the issue, below is a little script I came up which reads the list of tables with their CLOB column and then converts them. the key element is dbms_lob.getlength($ColumnName)=0 .

Hope this helps.

--Seena

#####################################################

set TABLES_COLUMNS_List [list \
\
[list ACTIVITY_GUIDE_INFO              JAVASCRIPT            ] \
[list BOOKS                            BOOK_DESCRIPTION      ] \
]

set db [ns_db gethandle]

foreach TableColumn $TABLES_COLUMNS_List {

    set TableName  [lindex $TableColumn 0]
    set ColumnName [lindex $TableColumn 1]

    set SQL {
        --
        SELECT count(rowid) FROM $TableName
        WHERE dbms_lob.getlength($ColumnName)=0
    }

    set UpdateSQL [subst { UPDATE $TableName SET $ColumnName=NULL WHERE dbms_lob.getlength($ColumnName)=0 } ]

    ns_db dml $db $UpdateSQL]

}

ns_db releasehandle $db

############################################################