| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> BLOB Again !
All,
I have still some question regarding BLOB objects if you don't mind to answer with some real experience:
I have a table with ID number & Content BLOB which this table will populate by a calling store procedure with the application, the application call this store procedure and send 5 to 40 Meg files thru ODBC connection to the database, so My store procedure will have One Input parameter as BLOB then genereare an ID and store the ID & BLOB into a table, but my question is How do I have to control the other parameters like: Buffer, Amount & Position as the BLOB size passing thru the application is not always the same size:
This is the Store Procedure which I wrote : If you have any idea please let me know Appreciate:
My question is how to control the buffer & amount & position??
CREATE OR REPLACE procedure s_insert_blob(file_blob IN BLOB,batch_file_id
OUT FILE_BLOB_temp.BATCH_FILE_ID%type)
as
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_contenet) 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;
for i in 1 ..3 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;
/
Hamid Alavi
Office : 818-737-0526
Cell phone : 818-416-5095
 |
 |