Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    17

    Unanswered: Oracle Clob Zero Length Issues with Resultsets

    Hi All,

    Environment:
    OS: Windows2000 SP4
    Oracle: 10.1.0.2.0

    I have a question about Oracle clobs, more specifically their lengths. We're having a problem with a result set implementation (a custom one we wrote). I've searched the forums and oracles metalink to no avail.

    I start by inserting a Clob with an empty string into the Oracle Database (ie: <INSERT INTO EMP (C, I, CLOBATTR) VALUES (100, 100, '')>). Our result set impl handles clobs by doing a get object on the column in the result set row as it iterates through the cols/rows. When i have an Clob of length zero, the object that is returned is just an object (can't seem to cast it to anything), however, when the length of the Clob is > 0, the object returned is of type java.sql.Clob (subsitute the <''> in the above SQL insert statement with a short test string (44 characters long)). Anyone know why the Clob of zero length comes back as a seemingly plain old object? Thanks for reading this.

    Jason.

    P.S. I have not had the above problem with DB2 v9 FP2.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    What if you specify empty_clob() instead of an empty string (ie: <INSERT INTO EMP (C, I, CLOBATTR) VALUES (100, 100, empty_clob())>) ? That should create ... well... a clob that is empty , but instantiated.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Apr 2007
    Posts
    17
    Thank you for the prompt reply RBARAER. I have tried that and it works fine. Can i assume that empty_clob() is the same as inserting <''> into a clob column in the db? It doesn't appear to like the <''> - seems to be a behavioural thing with either oracle db or its drivers or both. Thanks again,

    Jason.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by surfjungle
    Thank you for the prompt reply RBARAER. I have tried that and it works fine. Can i assume that empty_clob() is the same as inserting <''> into a clob column in the db? It doesn't appear to like the <''> - seems to be a behavioural thing with either oracle db or its drivers or both. Thanks again,

    Jason.
    Here is the difference between a NULL and an EMPTY large object (BLOB or CLOB). This has to do with the way LOBs are managed in the database, this is not a driver's issue.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Apr 2007
    Posts
    17
    Hi RBARAER,

    Once again, my thanks to you. So, using <''> as the value to insert into a clob is actually setting the clob to null, and not empty - I did not know that. So, if I want to set the clob to an empty string, or just empty - I should be using the <empty_clob()> function instead. Thanks again for clearing that up.

    Jason.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by surfjungle
    So, using <''> as the value to insert into a clob is actually setting the clob to null, and not empty - I did not know that.
    That's nothing special to CLOBs, that's how Oracle treats character literals. A literal with the length zero (aka "empty string") is NULL.

    http://download-uk.oracle.com/docs/c...005.htm#i59110

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by shammat
    That's nothing special to CLOBs, that's how Oracle treats character literals. A literal with the length zero (aka "empty string") is NULL.

    http://download-uk.oracle.com/docs/c...005.htm#i59110
    In a way you are right, but not completely .

    Okay, Oracle treats empty strings as NULL (which is by the way a BIG CONCEPTION MISTAKE that they are keeping from version to version because it must be very complicated to change, but I will be very happy when they change that), so we have to deal with that for character types as CHAR, VARCHAR, VARCHAR2... which are simple datatypes. Now when talking about CLOBs, we are not dealing with a simple datatype anymore : have you seen an entire Oracle doc about VARCHAR2 ??? No of course, but yet there is one about Large Objects, BLOBs and CLOBs, because they are really special datatypes. See what the OP said here : OK an empty string inserted into a CLOB corresponded to a NULL value, but if it had been a VARCHAR2 column, a CAST would have worked and casted it to VARCHAR2... Here, nothing, because NULL means NO LOCATOR for a CLOB : CLOBs, on the contrary to VARCHAR2s, have an "empty string" state that IS a value, not NULL. This whole " '' IS NULL " thing in Oracle is really misleading and despite what you said, I would not have taken for granted the fact that what Oracle says about empty strings and NULL in the SQL reference also concerns CLOBs. As there IS an EMPTY state for CLOBs, Oracle could have decided to treat '' as empty_clob() instead of treating it as NULL.

    In the end you are right, but I wanted to point out that it is more complicated that what you seemed to say .

    Best regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Apr 2007
    Posts
    17
    Once again thanks for the above, I see the differences now - things aren't as straightforward as they appear. I'll be wary when deailing with *lobs in the future. Thanks again,

    Jason.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •