How to insert (append) VARCHAR2 in LOB columns
I am working on Oracle Text search and Context indexes.
At one point we have to merge VARCHAR2 columns with LOB columns to build a composite index.
Oracle's document -
...suggests to use a user-defined function "append_varchar_to_lob" to use that will Append a Varchar column at the end of the LOB but it does not show how to do it.
You can't simply concatenate LOB and VARCHAR2 because of the datatype inconsistency.
The TO_LOB function works only on the LONG columns and that too with many restrictions.
I will really appreciate any help on this.
I think the DBMS_LOB.WRITEAPPEND procedure might help you.
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount IN BINARY_INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
lob_loc - Locator for the internal LOB to be written to.
amount - Number of bytes (for BLOBs) or characters (for CLOBs) to write, or number that were written.
buffer - Input buffer for the write.
The whole difference between a little boy and an adult man is the price of toys
thank you; I too figured this out;
and for anyone interested...there is a package DBMS_LOB with functions like WRITE and APPEND to do this job.
Click Here to Expand Forum to Full Width