Loading
Friday, June 14, 2013
We
had a requirement to download an AP Invoice attachment stored in BLOB column
from the database. We wrote a standalone procedure to accomplish the same.
Script:
CREATE OR REPLACE PROCEDURE xx_unload_ecx_attachment
(p_msg_id IN VARCHAR2,
p_inv_image_dir IN VARCHAR2
) IS
CURSOR cur_new_attmt IS
SELECT fl.file_id,
fl.file_name,
fl.file_data,
dbms_lob.getlength(fl.file_data) file_length
FROM fnd_lobs fl,
ecx_attachment_maps eam
WHERE eam.fid = fl.file_id
AND eam.msgid = p_msg_id;
v_start NUMBER DEFAULT 1;
v_bytelen NUMBER DEFAULT 32000;
v_len_copy NUMBER;
v_raw_var RAW(32000);
v_output utl_file.file_type;
v_inv_file_name VARCHAR2(100);
v_position NUMBER;
v_first_rec BOOLEAN DEFAULT TRUE;
BEGIN
v_position := 10;
FOR rec_inv IN cur_new_attmt
LOOP
BEGIN
v_inv_file_name := NULL;
v_inv_file_name := rec_inv.file_name;
v_position := 20;
-- define output directory
AND OPEN THE file IN WRITE BYTE MODE
v_output
:= utl_file.fopen(p_inv_image_dir,
v_inv_file_name,
'wb',
32760);
v_position := 30;
-- maximum size OF buffer
parameter IS 32767 BEFORE
-- which you have TO
flush your buffer
IF rec_inv.file_length < 32760
THEN
utl_file.put_raw(v_output,
rec_inv.file_data);
utl_file.fflush(v_output);
ELSE
v_position := 40;
v_start :=
1;
v_bytelen := 32000;
v_len_copy := rec_inv.file_length;
WHILE v_start < rec_inv.file_length
AND v_bytelen > 0
LOOP
v_position := 50;
dbms_lob.READ(rec_inv.file_data,
v_bytelen,
v_start,
v_raw_var);
v_position := 60;
utl_file.put_raw(v_output,
v_raw_var);
v_position := 70;
utl_file.fflush(v_output);
v_start := v_start + v_bytelen;
v_len_copy := v_len_copy - v_bytelen;
IF v_len_copy < 32000
THEN
v_bytelen := v_len_copy;
END IF;
END LOOP;
v_position := 80;
utl_file.fclose(v_output);
END IF;
v_position := 90;
dbms_output.put_line(' File_name :' || rec_inv.file_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(rpad(nvl(v_inv_file_name,'NA'),31)
|| rpad('ERROR', 21)
|| 'POSITION: '
|| v_position
|| 'Error :'
|| substr(SQLERRM,1,100));
END;
END LOOP;
END xx_unload_ecx_attachment;
Friday, June 14, 2013 by Team search · 0
Subscribe to:
Posts (Atom)
