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