CLOB's unexpected ways

3

CLOBs (Character Lobs) are the succesor of Oracle’s not-so-helpful LONG datatype. It is possible for example to search through CLOB fields the same way as you do through VARCHAR2 fields. Also, text conversion functions can be  applied on CLOB colunms.

 But, there are some pitfalls.

 ....

Look at this:

SQL> desc clob_test

Name           Type         Nullable Default Comments
————– ———— ——– ——- ——–
ID             NUMBER(7)                             
NAME           VARCHAR2(20)                          
DESCRIPTION    CLOB         Y                        

SQL> update clob_test set name = REPLACE( name, ‘ABCD’, ‘ABCDEF’ ) where ID = 1;

 1 row updated

SQL> update clob_test set description = REPLACE( description, ‘ABCD’, ‘ABCDEF’ ) where ID = 1;

ORA-22921: length of input buffer is smaller than amount requested

 

So, CLOB behaves differently than VARCHAR2 when used in an Oracle function. It’s possible to work around it, of course:

 
 SQL> update clob_test set description = REPLACE( description||’  ‘, ‘ABCD’, ‘ABCDEF’ ) where ID = 1;

1 row updated.

 

Adding some blanks to the input variabele of the REPLACE function does the trick.

 

…..

 

No it doesn’t, because carriage returns disappear when a function is applied on a CLOB! I’ve been trying to work around this problem, but did not find a solution yet. I guess the solution lies in explicit conversion through the TO_CLOB function.

The lesson learned already is, that you have to be really carefull when applying Oracle tekst conversion functions on CLOBs. The way data is stored in the database is significantly different for VARCHAR2 and CLOB.

For VARCHAR2, the space needed for all the values in a record is allocated in the datablock, with some slack to accomodate future updates which require more space (for instance, when you update ‘ABCD’ with ‘ABCDEF’ in the example above). If that is not enough, row migration or even row chaining will occur.
In the case of CLOBs, Oracle will always create a pointer to a different location on disk (by default in the same tablespace as the corresponding table) where the contents of the CLOB is stored as soon as the size of the LOB exceeds plusminus 4000 bytes.
So, even if the internal storage of these two datatypes is different, one would expect that the behaviour to the programmer would be more or less the same. This is apparently not the case.

Followup:

The replace function returns Varchar2 when the arguments given to it are Varchar2, but does return a CLOB when its arguments are CLOB.

So:

SQL> select to_char( replace( to_clob( ‘abcdef’||chr(10)||’ghijk’), to_clob( ‘ab’ ), to_clob( ‘zz’) ) ) test from dual;

TEST
—————————–
zzcdef
ghijk

This should do the trick after all.

 

Share.

About Author

3 Comments

  1. Patrick Sinke on

    Hi Marco, thanks for you information, as always ;)
    I forgot to mention I was working in Oracle 9iR2, apparently it’s al a bit less buggy in 10g!

  2. Marco Gralike on

    Also look at “Storing CLOBs Inline: DISABLING STORAGE and Space Used” on http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96591/adl06faq.htm

    The link for the REPLACE statements should have been http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm#SQLRF00697

    Chaining or migration can be tollerable as long as your (storage/architecture) design is correct. See also:

    http://asktom.oracle.com/pls/ask/f?p=4950:8:18100613647296170221::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:358341515662

  3. Marco Gralike on

    See “lob_replace” on asktom for a solution: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1533006062995

    After looking up replace, what rules apply for input / ouput datatypes (http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm#SQLRF00697)
    I did the following:

    SQL> select * from v$version;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for Linux: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

    5 rows selected.

    SQL> create table clob_test
    2 (id number(7),
    3 name varchar2(20),
    4 description clob
    5 );

    Table created.

    SQL> insert into clob_test
    2 values (1, ‘ABCD’,'ABCD’)
    3 ;

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> update clob_test set name = REPLACE( name, ‘ABCD’, ‘ABCDEF’) where ID=1
    2 ;

    1 row updated.

    SQL> update clob_test set description= REPLACE( description, ‘ABCD’, ‘ABCDEF’) where ID=1;

    1 row updated.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF

    1 row selected.

    SQL> insert into clob_test
    2 values (2, ‘ABCD’,to_clob(‘ABCD’))
    3 ;

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF
    2 ABCD ABCD

    2 rows selected.

    SQL> update clob_test set description= REPLACE( description, ‘ABCD’, ‘ABCDEF’) where ID=2;

    1 row updated.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF
    2 ABCD ABCDEF

    2 rows selected.

    SQL> update clob_test set description= REPLACE( description, ‘ABCD’, to_clob(‘ABCD’)) where ID=2;

    1 row updated.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF
    2 ABCD ABCDEF

    2 rows selected.

    SQL> update clob_test set description= REPLACE( description, ‘ABC’, to_clob(‘ABCD’)) where ID=2;

    1 row updated.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF
    2 ABCD ABCDDEF

    2 rows selected.

    SQL> update clob_test set description= REPLACE( description, to_clob(‘DD’),to_clob(‘D’)) where ID=2;

    1 row updated.

    SQL> select * from clob_test;

    ID NAME DESCRIPTION
    ——— ——————– ——————————————————————————-
    1 ABCDEF ABCDEF
    2 ABCD ABCDEF

    2 rows selected.