LOB size restricted under 4k in procedure or trigger?

    I am working on Oracle 8.1.5 on Windows NT4. I found that the size of LOB is restricted under 4K when the LOB is passed as parameter to stored procedures. In C++ Client/Server programming using OO4O and ODBC, this restriction can be overcome, though quite painfully. But in a trigger, the LOB field passed in the record NEW only contains no more than 4K. I can not see an easy work-around for duplicating LOB in a trigger.

    Since I am new to Oracle programming. Your corrections and comments are welcome.

    This problem seems to occur at PL/SQL level, since I found the ODBC call SQLPutData and SQLGetData did not help.

    Strangely, this problem will NOT happen if you code in VB using OO4O. In VB/OO4O, LOB behaves extremely normal, just like VARCHAR2. You can get the whole LOB in one call (I used ADO), no matter how long it is. That makes me think it may be a bug created by Oracle (though possibly a mis-use on my part).

    In a trigger, you can run DBMS_LOB.getlength(:NEW.field_CLOB). In the case of original field_CLOB having a size more than 4,000, it always returns 4,000. I also tried using DBMS_LOB.copy on it, only less than 4,000 is copied. I think this is a clear sign that LOB is passed no more than 4,000 bytes into a trigger.

    I have the impression that a LOB with less than 4,000 bytes is stored in the same block as the rest of the row. A LOB with a size of larger than 4,000 has the remaining bytes stored in another area. A pointer to that area is stored inside the LOB locator. Is it possible that Oracle ignored the pointer when it passed the LOB into a trigger? Only a wild guess.

    Please share your experience.

                                       Dong Liu

PS: Work-around in C++ Client/Server programming (detail ignored):

    SQLBindParameter(hstmt, ID_PARAMETER, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, min(4000, strlen), 0, pszLongString, min(4000, strlen), &cbStrLen);

    /* At the first time, do not pass more than 4000 characters (for CLOB). Then SQLExecDirect the insert or update clause or procedure. PL/SQL will correctly convert the "IN VARCHAR2" to CLOB field, as long as the length is under 4,000*/

    Then you need to do a while loop, within which:

    SQLBindParameter(hstmt, ID_PARAMETER, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, min(4000, strlen_remained), 0, (pszLongString + char_stored), min(4000, strlen_remained), &cbStrLen);

    /* The length (min(4000, strlen_remained)) needs also be passed in. Then SQLExecDirect the stored procedure, which has:

(declare) clob_loc CLOB;
(assign the clob_lob) SELECT field_CLOB into clob_loc ...
    DBMS_LOB.write(clob_loc, length_passed_in, DBMS_LOB.getlength (clob_loc) + 1, buffer_passed_in)*/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 28 1999 - 17:26:25 CDT