Because Humans Need Breaks, working with documents, part 3

Now that we have covered everything related to document management in our previous articles, the next step is to implement the file download functionality, allowing users to retrieve and view the files stored in our repository. Additionally, it is important to note that for files of type “assistant and messages”, we need to block the download functionality, as these files cannot be downloaded.

The reason for this restriction is that “assistant and messages” files typically contain dynamically generated content or sensitive information tied to conversational context, which is not meant to be directly accessed or retrieved in raw form. Allowing downloads of such files could lead to data mismanagement, potential breaches of confidentiality, or misuse of dynamically generated content that is only relevant within its intended application environment.

We will implement some changes in how we create the link, and this time, we will do it from the SQL query section of the interactive report to customize the messages displayed to the user.

Copy to Clipboard

SELECT 
 -- Unique identifier of the file
 ID,
 -- Object type
 OBJECT AS OBJECT_TYPE,
 -- File size in bytes
 BYTES AS SIZE_IN_BYTES,
 -- File size in megabytes (formatted)
 TO_CHAR(BYTES / 1048576, 'FM999990.00') AS SIZE_IN_MB,
 -- Creation date in UNIX format (seconds since 1970-01-01)
 CREATED_AT,
 -- Convert `CREATED_AT` to a human-readable Oracle timestamp
 TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(CREATED_AT, 'SECOND') AS CREATION_DATE,
 -- File name
 FILENAME AS FILE_NAME,
 -- Translates the file purpose using `apex_lang.message`
 CASE 
 WHEN PURPOSE = 'assistants' THEN apex_lang.message('GIA_ASSISTANTS_AND_MESSAGE')
 WHEN PURPOSE = 'vision' THEN apex_lang.message('GIA_ASSISTANTS_IMAGE')
 WHEN PURPOSE = 'batch' THEN apex_lang.message('GIA_BATCH_API')
 WHEN PURPOSE = 'fine-tune' THEN apex_lang.message('GIA_FINE_TUNING')
 ELSE PURPOSE
 END AS PURPOSE,
 -- File status
 STATUS,
 -- Additional status details
 STATUS_DETAIL,
 -- Download link with restrictions based on file purpose
 CASE 
 -- If the purpose is 'assistants', disable download and display a grayed-out icon
 WHEN PURPOSE = 'assistants' THEN 
 '<a href="#" role="link" aria-label="' || apex_lang.message('GIA_DOWNLOAD_FILE', FILENAME) || '" ' ||
 'title="' || apex_lang.message('GIA_NOT_ALLOWED_DOWNLOAD_ASSISTANTS', apex_lang.message('GIA_ASSISTANTS_AND_MESSAGE')) || '">' ||
 '' || 
 '</a>'

-- Enable downloads for allowed purposes ('vision', 'batch', 'fine-tune')
 WHEN PURPOSE IN ('vision', 'batch', 'fine-tune') THEN 
 '<a href="#" onclick="apex.event.trigger(document, ''downloadFile'', ''' || ID || ''');" ' ||
 'role="link" aria-label="' || apex_lang.message('GIA_DOWNLOAD_FILE', FILENAME) || '" ' ||
 'title="' || apex_lang.message('GIA_DOWNLOAD_FILE', FILENAME) || '">' ||
 '' ||
 '</a>'
 ELSE ''
 END AS DOWNLOAD_LINK,
 -- Delete link with dynamic APEX event trigger
 '<a href="#" onclick="apex.event.trigger(document, ''deleteFile'', ''' || ID || ''');" ' ||
 'role="link" aria-label="' || apex_lang.message('GIA_DELETE_FILE', FILENAME) || '" ' ||
 'title="' || apex_lang.message('GIA_DELETE_FILE', FILENAME) || '">' ||
 '' ||
 '</a>' AS DELETE_LINK
FROM 
 #APEX$SOURCE_DATA#;

The onclick event in DELETE_LINK triggers the custom event 'deleteFile' in APEX using:

apex.event.trigger(document, 'deleteFile', ID);

For this to work, the Custom Event Name in the APEX Dynamic Action must match exactly ('deleteFile'), and the event should be set to trigger on document to capture the action globally.

If the event name differs in APEX (e.g., typos, extra spaces, or different casing), the delete action won’t execute. Keeping this consistency ensures that APEX correctly processes the file deletion based on the passed ID.

Oracle APEX Page Designer interface showing a dynamic action setup for downloading files, with a custom event 'downloadFile' highlighted in red.

An Oracle APEX Page Designer view illustrating the configuration of the ‘DA_DOWNLOAD_FILE’ dynamic action, with the custom event ‘downloadFile’ highlighted for triggering file download processes

As seen in the image, the Dynamic Action DA_DOWNLOAD_FILE follows a structured process with four key steps to handle file downloads in Oracle APEX efficiently:

SET FILE ID: Assigns the selected file’s ID to a page item (:P20010_FILE_ID).

Oracle APEX Page Designer interface displaying the 'SET FILE ID' dynamic action configuration with JavaScript expression 'this.data' to set the file ID dynamically.

Oracle APEX Page Designer view highlighting the configuration of the ‘SET FILE ID’ action, which uses a JavaScript expression to dynamically set the file ID

PR_GET_FILE_NAME: Calls a REST API to fetch the filename associated with the file ID and stores it in :P20010_FILE_NAME.

Copy to Clipboard

DECLARE
    -- Variable to hold the parameters for the REST source
    v_params_metadata apex_exec.t_parameters; 
    
    -- Variable to store the filename extracted from the response
    v_filename         VARCHAR2(255);
    
    -- Variable to store the JSON response from the REST call
    l_response         CLOB;
BEGIN
    -- Add the 'file_id' parameter to the REST call, using the value from page item :P20010_FILE_ID
    apex_exec.add_parameter(v_params_metadata, 'file_id', :P20010_FILE_ID);

-- Execute the REST source with the specified Static ID and HTTP GET operation
    apex_exec.execute_rest_source(
        p_static_id  => 'GIA_OPENAI_FILES_SINGLE_ROW',
        p_operation  => 'GET',
        p_parameters => v_params_metadata
    );

-- Check if the REST call was successful
    IF apex_web_service.g_status_code = 200 THEN
        -- Retrieve the JSON response from the executed REST source
        l_response := apex_exec.get_parameter_clob(v_params_metadata, 'response');
        
        -- Parse the JSON response to make it accessible for data extraction
        apex_json.parse(l_response);
        
        -- Extract the 'filename' attribute from the JSON object
        v_filename := apex_json.get_varchar2('filename');
        
        -- Assign the extracted filename to the page item :P20010_FILE_NAME
        :P20010_FILE_NAME := v_filename;
    ELSE
        -- If the REST call fails, display a user-friendly error message
        apex_error.add_error(
            p_message          => apex_lang.message('GIA_ERROR_FILE_METADATA'),
            p_display_location => apex_error.c_on_error_page
        );

-- Set the page item :P20010_FILE_NAME to NULL in case of an error
        :P20010_FILE_NAME := NULL;
    END IF;
END;

It is important to remember that in “Items to Submit”, we must reference the page item that needs to be sent to the PL/SQL process—in this case, P20010_FILE_ID, which is required to fetch the file name.

Oracle APEX Page Designer interface showing the 'PR_GET_FILE_NAME' process, which uses PL/SQL code to retrieve a file name via a REST API call, with specific items to submit and return highlighted

Oracle APEX Page Designer view featuring the ‘PR_GET_FILE_NAME’ process, configured to fetch a file name using PL/SQL and REST API integration, with P20010_FILE_ID and P20010_FILE_NAME set as the items to submit and return

Similarly, in “Items to Return”, we must specify where the retrieved data will be stored—in this case, P20010_FILE_NAME, which will hold the filename extracted from the REST API response.

PR_DOWNLOAD_FILE: Retrieves the file’s content using the OpenAI API, stores it in an APEX collection (FILE_DOWNLOAD_COLLECTION) along with the filename.

Copy to Clipboard

DECLARE
    -- Constants for REST API headers and values
    c_authorization_header CONSTANT VARCHAR2(50) := 'Authorization';
    c_bearer_prefix        CONSTANT VARCHAR2(10) := 'Bearer';
    c_space                CONSTANT VARCHAR2(1)  := ' ';
    c_content_type_header  CONSTANT VARCHAR2(50) := 'Content-Type'; 
    c_content_type_value   CONSTANT VARCHAR2(50) := 'multipart/form-data';

-- Constants for API endpoint and HTTP method
    c_api_url              CONSTANT VARCHAR2(200) := 'https://api.openai.com/v1/files/';
    c_api_url_content      CONSTANT VARCHAR2(50) := '/content';
    c_http_method_get      CONSTANT VARCHAR2(10) := 'GET';

-- Variables for file content and metadata retrieval
    v_file_content         BLOB;
    v_status_code          NUMBER;
    v_file_name            VARCHAR2(255);
BEGIN
    -- Step 1: Retrieve the filename from the page item
    v_file_name := :P20010_FILE_NAME;
    
    -- Clear and set REST API request headers
    apex_web_service.g_request_headers.delete();
    apex_web_service.g_request_headers(1).name  := c_authorization_header;
    apex_web_service.g_request_headers(1).value := c_bearer_prefix || c_space || APEX_APP_SETTING.GET_VALUE('GIA_OPENAI_API_KEY');
    apex_web_service.g_request_headers(2).name  := c_content_type_header;
    apex_web_service.g_request_headers(2).value := c_content_type_value;

v_file_content := apex_web_service.make_rest_request_b(
        p_url         => c_api_url || :P20010_FILE_ID || c_api_url_content,
        p_http_method => c_http_method_get
    );

-- Get the HTTP response status code
    v_status_code := apex_web_service.g_status_code;

-- Step 3: Check if the file content download was successful
    IF v_status_code = 200 THEN
        -- Step 4: Add the file content to an APEX collection
        apex_collection.create_or_truncate_collection('FILE_DOWNLOAD_COLLECTION');

apex_collection.add_member(
            p_collection_name => 'FILE_DOWNLOAD_COLLECTION',
            p_c001            => v_file_name,
            p_blob001         => v_file_content
        );
    ELSE
        apex_error.add_error(
            p_message         => apex_lang.message('GIA_ERROR_FILE_DOWNLOAD'),
            p_display_location => apex_error.c_on_error_page
        );
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        apex_error.add_error(
            p_message         => apex_lang.message('GIA_UNEXPECTED_ERROR'),
            p_display_location => apex_error.c_on_error_page
        );
END;

For the download process, we only need to submit P20010_FILE_ID and P20010_FILE_NAME, as these are the essential fields used to retrieve and store the file.

Oracle APEX Page Designer interface showcasing the 'PR_DOWNLOAD_FILE' process, which uses PL/SQL code to handle REST API calls for file downloads, with defined constants and items to submit and return.

Oracle APEX Page Designer configuration of the ‘PR_DOWNLOAD_FILE’ process, designed to retrieve file content from a REST API and prepare it for user download using server-side PL/SQL logic

DOWNLOAD: A new APEX 24.1 feature that simplifies file downloads. It reads the file from the collection and serves it to the user with minimal configuration, requiring only a SQL query with columns for the file content (BLOB), filename, and MIME type.

Copy to Clipboard

Oracle APEX Page Designer interface displaying the 'DOWNLOAD' action configuration, which retrieves file content from an APEX collection using an SQL query, with settings for file download as an attachment

Oracle APEX Page Designer setup for the ‘DOWNLOAD’ action, using an SQL query to fetch file content, name, and MIME type from an APEX collection for seamless file download

This streamlined approach ensures efficient file retrieval and downloading, leveraging APEX collections for temporary storage and REST API integration for file access.

For more information, please visit our company website or find us on APEX World for additional resources and updates!