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.
![Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases")
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!
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
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.