CLOB's unexpected ways
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
————– ———— ——– ——- ——–
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.
The replace function returns Varchar2 when the arguments given to it are Varchar2, but does return a CLOB when its arguments are CLOB.
SQL> select to_char( replace( to_clob( ‘abcdef’||chr(10)||’ghijk’), to_clob( ‘ab’ ), to_clob( ‘zz’) ) ) test from dual;
This should do the trick after all.