I tried to use declare 2 v_lob clob; 3 begin 4 5 for r_lob in (select c 6 from foo 7 for update) 8 loop 9 dbms_lob.write(r_lob.c, 6, 16, 'phrase'); -- ie write at offset 16, 6 bytes 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed.Last updated on NOVEMBER 23, 2017 Oracle Database - Enterprise Edition - Version 126.96.36.199 and later Information in this document applies to any platform.WRITEAPPEND(temp_clob,replace_str_len,replace_str); END IF; END LOOP; IF LENGTH(search_str) Thanks Julian that worked a treat!Not very good with pl/sql yet so it does help when I get pointed in the right direction, although understanding what it was doing will take me a little longer to work out!!! In my case, I copied the text (XML) from the source column in the source database and pasted it to a file.The file handler is “EXAMPLE_LOB_DIR” and the directory is “/directory/to/filter.xml”. Update Column In Database Now that we have the file handler, we’ll use the chunk of SQL Syntax below, to read the XML File into a SQL UPDATE statement, which updates DATA(column) in the CONFIG(table) with data in the dest_clob(xml file).
Submit a bug or feature For further API reference and developer documentation, see Java SE Documentation. Hi, using Result Set.update Character Stream to update a CLOB is very slow.Most of the time is spent in method Pg Result Updateable because the table has a primary key but doesn't have a "oid" column. IF YOU ARE NOT USING ORACLE 9i R2 AND/OR DO NOT NEED — TO SUPPORT LOADING TO A MULTI-BYTE CHARACTER SET DATABASE, USE THE — FOLLOWING FOR LOADING FROM A FILE: — — DBMS_LOB. GETLENGTH(src_clob) — ); — — ———————————————————————– SELECT DATA INTO dest_clob FROM CONFIGS WHERE UNIT = ‘filter’ FOR UPDATE; — ————————————- — OPENING THE SOURCE BFILE IS MANDATORY — ————————————- DBMS_LOB. If you are not sure what I am aksing, please do not give negetive feedback.The restrictions in place made it impossible to edit the fields using utilities such as DBVIS or Toad for Oracle.I wanted to be able to write SQL Statements that allowed files to be read into the stream and update the backend database column (CLOB).With some MSSQL and My SQL skills, I did some reading up on how Oracle can manipulate CLOB/LOB Columns using DBMS_LOB.The result is a simple SQL Statement that results in an SQL UPDATE statement, which updates a column, with XML located in /directory/to/ Create Directory Object To start with, you’ll need to place the XML file in a directory and create the File Handler in Oracle for use later.DECLARE dest_clob CLOB; src_clob BFILE := BFILENAME(‘EXAMPLE_LOB_DIR’, ‘filter.xml’); dst_offset number := 1 ; src_offset number := 1 ; lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX; warning number; BEGIN DBMS_OUTPUT. ENABLE(100000); — ———————————————————————– — THE FOLLOWING BLOCK OF CODE WILL ATTEMPT TO INSERT / WRITE THE CONTENTS — OF AN XML FILE TO A CLOB COLUMN. Load CLOBFrom File() API WHICH *DOES* SUPPORT MULTI-BYTE — CHARACTER SET DATA.