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

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

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