CLOB Store procedure

From: Hamid Alavi <hamid.alavi_at_quovadx.com>
Date: Thu, 1 Jul 2004 14:21:50 -0500
Message-ID: <C398496DF88AD711908C00065BEEE32CFE9C24@dfwmail.quovadx.com>

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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Thu Jul 01 2004 - 14:27:48 CDT