Oracle provides PeopleTools developers with the ability to store files with transactions using the File Attachment API. Likewise, the File Attachment API includes PeopleCode functions for extracting and displaying attachments. What if you need to export attachments directly from a database or from SQR where you don't have access to the File Attachment PeopleCode functions? Here is some PL/SQL demonstrating how to export attachments. You just need to set the file name and the attachment record name. I labeled the values you need to change with
TODO:. Code for other databases should be relatively similar.
CREATE OR REPLACE DIRECTORY TEMP_DIR AS 'c:\temp'
-- Max PS attachment chunk size is 28000
CV_BUFFER_MAX NUMBER := 28000;
-- Name of file to export. This is the name that was used to store the file
-- and is the name that will be used to create a new file.
-- TODO: change the name to match the name of your exported file
lv_file_name VARCHAR2(128) := 'theattachedfile.xls';
lv_file_ref := utl_file.fopen('TEMP_DIR', lv_file_name, 'WB');
FOR r_chunks IN
-- TODO: Change record to the name of your attachment record
WHERE ATTACHSYSFILENAME = lv_file_name) LOOP
dbms_lob.read(r_chunks.FILE_DATA, r_chunks.FILE_SIZE, 1, lv_buffer);
utl_file.put_raw(lv_file_ref, lv_buffer, true);
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(lv_file_ref) THEN
You will notice that this code exports an attachment to the database server's file system.
How do you find file attachment records? The easiest way I can think of is to query the
PSRECFIELD table for all record definitions that contain the
FILE_ATTDET_SBR sub record. Here is the SQL:
WHERE FIELDNAME = 'FILE_ATTDET_SBR'