Updating a clob field
You can also perform more complicated updates in Oracle.You may wish to update records in one table based on values in another table.
= -1 ) String str XML = str String(); //WRITING THE CLOB TO ORACLE 8i Statement statement2 = connect.create Statement(); mglxxx i am trying to update a CLOB in my table, but somehow i am not getting any luck. CLOB DB2 CODE THAT I USED FOR CLOB READ/WRITE READING FROM A CLOB FIELD Connection connect = null; Prepared Statement prep Statement = null; Result Set rs = null; String str XML = ""; String get Items Query = "SELECT IT_XML " "FROM " Database Names. "; connect = get Connection(); prep Statement = connect.prepare Statement(g(); if (rs ! I am so glad that the code is working fine, and so is updation of the Clob field with more than 4000 characters.
Before you do (e.g.): SELECT resume INTO dst_file FROM sam_emp WHERE ID = 1 FOR update; You MUST have that "resume" column with ID=1 set to either EMPTY_BLOB() or some other valid BLOB locator. Many things are different with BLOBs comparing to scalar types, and that's because it's an object. You do not use UPDATE, you must use locators, empty_blob() function, DBMS_LOB package, etc etc...
If the record does not exist, I would do the folloving: ----------------------- INSERT INTO sam_emp (id, resume) VALUES (sam_emp_seq.nextval, empty_blob()) RETURNING id INTO my_id; COMMIT; SELECT resume INTO dst_file FROM sam_emp WHERE ID = my_id FOR update; dbms_lob.fileopen(src_file, dbms_lob.file_readonly); lgh_file := dbms_lob.getlength(src_file); dbms_lob.loadfromfile(dst_file, src_file, lgh_file); COMMIT; dbms_lob.fileclose(src_file); ----------------------- So, I introduce a few more elements here - sam_emp_seq - sequencer to generate ID values for sam_- id - column in sam_emp table that will be used as the primary key - empty_blob() - built-in function to set valid empty BLOB locator - my_id - a variable to hold the value of ID generated by the sequencer in INSERT statement. LOADFROMFILE procedure is doing the update for you. If you code an application, you will have to use streams to deal with BLOBs, while with VARCHAR2 you won't.
For (2),(3), // (5) and (6)pass the correct type of locator into the routine.
// For (4), remove the trigger body code that updates the LOB value.