RE: Anybody?

From: Hamid Alavi <hamid.alavi_at_quovadx.com>
Date: Mon, 7 Jun 2004 16:39:36 -0500
Message-ID: <C398496DF88AD711908C00065BEEE32CFE9A20@dfwmail.quovadx.com>

Here is my Store procedure to write in BLOB: CREATE OR REPLACE procedure sp_insert_blob(file_blob IN BLOB,file_unique_id IN FILE_BLOB_temp.FILE_UNIQUE_ID%type,batch_file_id OUT FILE_BLOB_temp.BATCH_FILE_ID%type)

lob_loc BLOB;

v_curr_val integer;

buffer varchar2(32767);

amount binary_integer :=32767;

position integer:=1;

I integer;

BEGIN INSERT INTO FILE_BLOB_temp(batch_file_id,file_content,file_unique_id)

values(seq_batch_file_id.nextval,EMPTY_BLOB(),file_unique_id);

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

-----Original Message-----

From: Fernando [mailto:fernandoluis_at_mac.com] Sent: Monday, June 07, 2004 1:53 PM
To: oracle-l_at_freelists.org
Subject: Re: Anybody?

IN DBMS_LOG.WRITE, amount is the # if bytes to write. You should control this to a smaller value than 32767 dynamically. In your code I couldn't find where you fill out BUFFER. That's where you should also control the value of AMOUNT.

DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY BLOB,

amount IN BINARY_INTEGER,

offset IN INTEGER,

buffer IN RAW);

DBMS_LOB.WRITE ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,

amount IN BINARY_INTEGER,

offset IN INTEGER,

buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);

lob_loc

Locator for the internal LOB to be written to. For more information, see Operational Notes.

amount

Number of bytes (for BLOBs) or characters (for CLOBs) to write, or number that were written.

offset

Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1) for the write operation.

buffer

Input buffer for the write.

On Jun 7, 2004, at 1:51 PM, Hamid Alavi wrote:

My question is: the file size between 5 to 40 Meg is there any way to check

the size of the file which passing thru the application then end the storing

blob or not?

right now I have defined buffer = 32767 may be this is too big is there any

way to define this smaller and check the actual file size then insert

another chunk till end or not?

Thanks,