Insert BLOB into a table

From: Hamid Alavi <hamid.alavi_at_quovadx.com>
Date: Mon, 28 Jun 2004 16:19:51 -0500
Message-ID: <C398496DF88AD711908C00065BEEE32CFE9B99@dfwmail.quovadx.com>

Hi List,

I have written a store procedure which insert a blob into a table, the BLOB pass as Input to this store procedure, I have compiled the store procedure and It looks ok, but when we call the store procedure thru the application to insert data it's failed: any body has any idea why, the error message is some thing like data has been truncated. Thanks for you help:

Here is the store procedure:

---

CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN BLOB, batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS

lob_loc   	  BLOB;
v_curr_val 	  INTEGER;
buffer 		  VARCHAR2(32767);
amount  	  BINARY_INTEGER :=32767;
position 	  INTEGER:=1;
I 			  INTEGER;

BEGIN SELECT DBMS_LOB.GETLENGTH(FILE_BLOB ) INTO amount FROM dual ;

INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_BLOB());

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;

LOOP
DBMS_LOB.WRITE (lob_loc,amount,position,buffer); position := position + amount;
END LOOP; EXCEPTION
WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('Operation failed End of Data');

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 Mon Jun 28 2004 - 16:07:56 CDT