CREATE DIRECTORY FPC_BLOBS AS '/export/home/apps_utl'
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT L.file_data
INTO l_blob
FROM fnd_attached_documents ad, fnd_documents_tl dt, fnd_documents d, fnd_lobs l
WHERE ad.pk1_value =5313672 --:customer_trx_id;
AND d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dt.LANGUAGE = 'US'
AND l.file_id = dt.media_id
and ad.seq_num=10;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('FPC_BLOBS','suresh.pdf','w', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
BEGIN
-- Get LOB locator
SELECT L.file_data
INTO l_blob
FROM fnd_attached_documents ad, fnd_documents_tl dt, fnd_documents d, fnd_lobs l
WHERE ad.pk1_value =5313672 --:customer_trx_id;
AND d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dt.LANGUAGE = 'US'
AND l.file_id = dt.media_id
and ad.seq_num=10;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('FPC_BLOBS','suresh.pdf','w', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
Thank you friend for sharing this oracle query. It was realy helpfull for me.
ReplyDeleteHi,
ReplyDeleteThanks for sharing the SQL . I am using the same SQL to download the Attachment, some are coming fine but many are downloaded 0 size.
Can you please check and advise.
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB ;
l_blob_len INTEGER;
l_file_name varchar2(100);
BEGIN
-- Get LOB locator
FOR rec IN (SELECT fl.file_name l_file_name,fl.file_data l_blob
FROM FND_ATTACHED_DOCS_FORM_VL v,
FND_LOBS fl
WHERE v.pk1_value IN (select src_invoice_id from XXUNITY_NAC_NOTES_CLIPS)
AND v.entity_name='AP_INVOICES'
AND fl.file_id=v.media_id
AND v.function_name='APXINWKB'
) LOOP
l_blob_len := DBMS_LOB.getlength(rec.l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('XXGFS_UNITY_NAC_DIR',rec.l_file_name,'w', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(rec.l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
UTL_FILE.fflush (l_file);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
Thanks,
Ritu
It's a perfect post. Very helpful thank you!
ReplyDeleteReally its a valuable information. And this will be helpful for many people and its an mandatory thing to be learned.
ReplyDeleteWeb Designing Training in Chennai
Really very nice blog information for this one and more technical skills are improve,i like that kind of post.
ReplyDeleteJava training in Jaya nagar
Java training in Electronic city
Java training in Chennai
Java training in USA