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).column in a table which holds very large amount of Xml Data. I need to update this column's value for one row of table. I have tried googling, but this visibly simple stuff is not available in a simple language anywhere. If I use the normal update query syntax and paste the huge xml content inside the single quotes (the single quote in xml content replaced with 2 single quotes), then the sql developer just disables the execute query button. Below are some *very* simple examples: CREATE OR REPLACE PROCEDURE PRC_WR_CLOB ( p_document IN VARCHAR2)ASBEGIN INSERT INTO TBL_CLOBHOLDERDDOC (CLOBHOLDERDDOC) VALUES (p_document); END;/CREATE OR REPLACE PROCEDURE PRC_UD_CLOB ( p_document IN VARCHAR2, p_id IN NUMBER)ASBEGIN UPDATE TBL_CLOBHOLDERDDOC SET CLOBHOLDERDDOC = p_document WHERE CLOBHOLDERDDOCID = p_id; END;/ Kyle, Ive a question for you.i got a clob field iam dispaying on webpage using oracle mod-plsql like htp.p(cursor1.clob-field, chr(10), as you can see iam trying to replace new line with br so the browser can understand but iam having problem if characters are more than 4000.Can anyone tell me how to update/alter some text that is held in a clob column?WRITE(lob_loc, LENGTH(p_document), 1, p_document); END; / CREATE OR REPLACE PROCEDURE PRC_UD_CLOB ( p_document IN VARCHAR2, p_id IN NUMBER) IS lob_loc CLOB; BEGIN SELECT CLOBHOLDERDDOC INTO lob_loc FROM TBL_CLOBHOLDERDDOC WHERE CLOBHOLDERDDOCID = p_id FOR UPDATE; DBMS_LOB.WRITE(lob_loc, LENGTH(p_document), 1, p_document); END; / CREATE OR REPLACE PROCEDURE PRC_RD_CLOB ( p_id IN NUMBER, p_clob OUT VARCHAR2) IS BEGIN SELECT CLOBHOLDERDDOC INTO p_clob FROM TBL_CLOBHOLDERDDOC WHERE CLOBHOLDERDDOCID = p_id; END; / Just wanted to update for anyone using newer releases of Oracle and have stumbled across this (like I did), you no longer have to use dbms_lob.write to write to CLOBs.
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.
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!!!
Not sure if you meant pl/sql or if you meant perl/sql, so here goes.
When using Perl and the DBD:: Oracle module you are able to select LOBs, CLOBs, and BLOBs as a normal field. You will need to tell DBD that you are inserting a LOB, similar to this: While this seems trivial, if you ever put something like a frozen data structure (via the Freeze Thaw module) in a CLOB, you'll end up getting errors all over the place when you later try to thaw it if the frozen structure happens to have whitespace at the end.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.