How to insert (append) VARCHAR2 in LOB columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to insert (append) VARCHAR2 in LOB columns

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    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
    Rajeev Suri

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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,
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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
    Rajeev Suri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width