Exporting Attachments Part 2

In my post Export PeopleSoft Attachments using PL/SQL, I mentioned that PeopleSoft provides the File Attachment API for storing, retrieving, and viewing file attachments. These API functions comprise the recommended method for working with attachments. If you want to process an attachment, use the GetAttachment function to move that attachment into a file so you can access it from your app server. The ViewAttachment function, on the other hand, will send a copy of an attachment to a client browser. The ViewAttachment function is the only method provided by the Attachment API that allows a user to view the contents of an attachment. What if you want more control over the way PeopleSoft displays attachments? For example, let's say you use the File Attachment API to allow selected users to upload audio files (news, recorded training sessions, etc) and you have another page that allows other users to listen to those recordings. Should the "view" page display a "View Attachment" link or should it play the selected clip as embedded audio?

If you store attachments in the database using a RECORD:// style URL, then you can extract those attachments using PeopleCode. Here is an IScript that demonstrates this:

Function IScript_GetAttachment()
   Local any &data;
   Local string &file_name = "attachment.doc";   
   Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name);

   REM Set the following header if you want a download prompt. Don't set it if you want inline content;
   %Response.SetHeader("content-disposition", "attachment;filename=" | &file_name);

   While &cursor.Fetch(&data);
      %Response.WriteBinary(&data);
   End-While;
End-Function;

This method allows you to embed audio/video, provide user configurable background images, display inline PDF files, etc.

If you follow my blog closely, you may remember that my IScripts post claims there is no way to write binary data to an HTTP response. Notice the use of the %Response.WriteBinary method above? I was wrong. I was reading the comments of the post Browse the App Server Filesystem via iScript and noticed Joe's reference to %Response.WriteBinary(). Even though it isn't documented in PeopleBooks, I tried it and it worked. Thanks Joe!

Note that I hard coded my SQL statement. I did this for simplicity in this blog post. For production systems, I encourage you to use SQL definitions. Of course, if you implement this solution, you will need to change the SQL select table name to the name of your attachment table. Likewise, you will probably derive your file name from a parameter rather than hard code it as shown here.

As with any custom development, make sure you write secure code. For example, when executing SQL based on parameters, use SQL binds. Failure to use SQL binds can result in SQL injection flaws. Likewise, if you accept an attachment from a user and then display that attachment using a means other than the provided ViewAttachment function, then make sure you either validate that input or validate the output. For example, let's say you have a page that allows users to upload JavaScript or other HTML that will be executed on other pages. Failure to restrict this type of usage provides malicious users with an opportunity to create HTML injection and cross site scripting vulnerabilities.

Export PeopleSoft Attachments using PL/SQL

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

DECLARE
  -- 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_buffer        RAW(28000);

  lv_file_ref      utl_file.file_type;
  
BEGIN
  lv_file_ref := utl_file.fopen('TEMP_DIR', lv_file_name, 'WB');

  FOR r_chunks IN
      (SELECT FILE_DATA
            , FILE_SIZE
     -- TODO: Change record to the name of your attachment record
         FROM PSFILE_ATTDET
        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);
  END LOOP;

  utl_file.fclose(lv_file_ref);

  EXCEPTION
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
      IF UTL_FILE.is_open(lv_file_ref) THEN
        UTL_FILE.fclose(lv_file_ref);
      END IF;
    RAISE;
END;
/

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:

SELECT *
  FROM PSRECFIELD
 WHERE FIELDNAME = 'FILE_ATTDET_SBR'