Hamid, My code to create the lob looks like this: IF fms_event_xml_count > 0 THEN v_log_location := 'CREATE LOB'; DBMS_LOB.createtemporary (v_xml_clob, TRUE); DBMS_LOB.OPEN (v_xml_clob, DBMS_LOB.lob_readwrite); FOR v_loop_index IN 1 .. fms_event_xml_count LOOP v_log_location := 'CREATE LOB:LOOP INDEX:' || v_loop_index; DBMS_OUTPUT.put_line (v_log_location); IF LENGTH (fms_event_xml_text (v_loop_index)) > 0 THEN DBMS_OUTPUT.put_line ('WRITEAPPEND'); DBMS_LOB.writeappend (v_xml_clob, LENGTH((fms_event_xml_text(v_loop_index)), fms_event_xml_text (v_loop_index)); END IF; END LOOP; END IF; Later when I go to store it I do this: IF pi_xml_message_text IS NOT NULL THEN g_log_location := 'Writing LOB XML_MESSAGE_TEXT to Database'; DBMS_APPLICATION_INFO.set_action (g_log_location); DBMS_LOB.OPEN (v_xml_message_text, DBMS_LOB.lob_readwrite); DBMS_LOB.COPY (v_xml_message_text, pi_xml_message_text, DBMS_LOB.getlength (pi_xml_message_text)); DBMS_LOB.CLOSE (v_xml_message_text); END IF; It might be that you never close the lob. The other issue may be that you issue a commit before you write the lob but after you have the handle. Try changing both. Also try creating the clob and then use COPY as in the example above, or you could try using WRITEAPPEND. The site that helped me most get through this is ASKTOM.ORACLE.COM. Take a look there as well. Hope that helps. Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@xxxxxxxxxxx] Sent: Thursday, July 01, 2004 3:50 PM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: CLOB Store procedure Jason, I have change it as follows, but when I run it doesn't insert the CLOB value into the table: Any Idea??? CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN VARCHAR2 , batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS lob_loc CLOB; v_curr_val INTEGER; buffer VARCHAR2(32000); amount BINARY_INTEGER :=32000; position INTEGER:=1; I INTEGER; BEGIN INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB() ) RETURNING file_content INTO lob_loc; COMMIT; FOR I IN 1 .. 3 LOOP DBMS_LOB.WRITE (lob_loc,amount,position,buffer); this line doesn't work properly!! position := position + amount; COMMIT; END LOOP; END; / -----Original Message----- From: Looney, Jason [mailto:Jason.Looney@xxxxxxxxxxxx] Sent: Thursday, July 01, 2004 1:27 PM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: CLOB Store procedure Something like this eliminates the overhead of the additional queries. INSERT INTO table_1 (col1, col2, clob_col) VALUES (col1_seq.nextval, p_col2, EMPTY_CLOB ()) RETURNING col1, clob_col INTO v_col1, v_clob_col; Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@xxxxxxxxxxx] Sent: Thursday, July 01, 2004 2:23 PM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: CLOB Store procedure RETURNING ? what do you mean is it insert the clob for you? -----Original Message----- From: Looney, Jason [mailto:Jason.Looney@xxxxxxxxxxxx] Sent: Thursday, July 01, 2004 1:10 PM To: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: CLOB Store procedure You could use the RETURNING clause so you don't have to get CURRVAL or your CLOB handle. Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@xxxxxxxxxxx] Sent: Thursday, July 01, 2004 1:22 PM To: 'Oracle-L (E-mail) Subject: CLOB Store procedure List, I have written this store procedure to store a CLOB Object into a table, Just want you guys look at it & if any thing wrong respond to it, appreciate yur help & time. Thanks, CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN CLOB, batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS lob_loc CLOB; v_curr_val INTEGER; buffer VARCHAR2(32000); amount BINARY_INTEGER :=32000; position INTEGER:=1; I INTEGER; BEGIN INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB()); COMMIT; SELECT seq_batch_file_id.CURRVAL INTO v_curr_val FROM dual; SELECT file_content INTO lob_loc FROM FILE_BLOB_TEMP WHERE batch_file_id = v_curr_val FOR UPDATE; FOR I IN 1 .. 3 LOOP DBMS_LOB.WRITE (lob_loc,amount,position,buffer); position := position + amount; COMMIT; END LOOP; END; / ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------