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

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

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

We experianced the same issue with Aolserver running on linux connecting to a Solaris Oracle 8i server. We were running v2.6 of the nsoracle driver. Upgrading to v2.7 solved our problem.