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 -
http://otn.oracle.com/products/text/...s/imt_815.html

...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.

Thanks,
- Rajeev

Hi,
I think the DBMS_LOB.WRITEAPPEND procedure might help you.

Code:

---

DBMS_LOB.WRITEAPPEND (
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.

---

Regards,

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.

- Rajeev