Because Humans Need Breaks, working with documents, part 1

Sometimes, before diving headfirst into code and technical specifics, it’s beneficial to step back and see the bigger picture. In this series of articles, we’ll explore how to integrate OpenAI’s capabilities with Oracle APEX to build a robust and customized file upload and management process. The goal is to facilitate secure and efficient interaction with the OpenAI API by leveraging the powerful tools of Oracle APEX while keeping a user-centric approach in mind.

In this first installment, we’ll focus on laying the groundwork for the integration: setting up a REST Data Source that connects to OpenAI’s /v1/files endpoint. This is a crucial first step, as it will enable us to display and manage information about the uploaded files (such as their IDs, statuses, and purposes) within an Interactive Report in Oracle APEX. Before we dive into the technical details, we’re establishing the framework necessary to build a solution that not only meets technical requirements but also offers an intuitive and friendly user experience.

Throughout this series, we’ll cover everything from file validation and pre-processing to error handling and adapting the API for specialized tasks like fine-tuning or batch processing. By balancing functionality and usability, we’ll discover how to optimize the interaction between enterprise applications and AI technologies.

The first step in integrating OpenAI with Oracle APEX is creating a REST Data Source that connects to the /v1/files endpoint. This will allow us to retrieve and display information about uploaded files (e.g., their IDs, statuses, and purposes) in an Interactive Report within Oracle APEX.

Rest Data Source GIA_OPENAI_FILES

We will use the REST Data Source GIA_OPENAI_FILES to create an Interactive Report in Oracle APEX. This report will display key fields from the /v1/files endpoint, such as File ID, File Name, Purpose, Status, File Size (KB), and Created Date.

Transformations will be applied, including converting file size to megabytes and formatting timestamps into readable date formats. Additional features like sorting, filtering (e.g., by Status or Purpose), and conditional formatting will be configured to enhance usability and provide a user-friendly interface for managing files retrieved from OpenAI.

Copy to Clipboard

One of my passions is psychology, and I’ve uploaded a couple of psychology books and an image to the repository for analysis by OpenAI. With the proper design, the user interface now displays the uploaded files in an organized and visually appealing format, showing essential details such as file names, purposes, and statuses.

user interface displays uploaded files

Additionally, I’ve added a button to prepare files for upload through a modal window, which allows users to validate and configure files before sending them to OpenAI. This setup ensures a smooth and user-friendly experience while leveraging OpenAI’s capabilities to analyze the uploaded content.

modal window to upload file

When the Upload button is clicked, a modal window will appear, allowing users to select the purpose of the file they wish to upload. The available purposes include:

Assistants and Message: This purpose is for uploading files with structured text content, such as messages or conversations, for analysis or AI-powered interaction. Examples include extracting insights, sentiment analysis, or preparing data for conversational AI tasks. File formats like TXT or CSV are typically used for this purpose.
Assistants Image: For uploading image files to perform analysis tasks like object detection, classification, or extracting text (OCR). This purpose enables AI-driven processing of visual data to extract meaningful insights. Supported formats often include JPG or PNG.
Batch API: This is designed for processing large datasets in bulk. Files must be in JSONL format (JSON Lines), where each line represents an individual data entry. This purpose is ideal for use cases like analyzing extensive customer datasets or processing structured data at scale.
Fine-Tuning: Fine-tuning allows users to train custom AI models using their own data. Files must be in JSONL format, with properly formatted entries containing the prompt and completion fields required for model training. This purpose is critical for tailoring OpenAI models to specific business or project needs.

Each option is clearly explained in the modal, ensuring users understand the requirements for their selected purpose. For example, Batch API and Fine-Tuning will validate that the file is in JSONL format before allowing the upload to proceed. This ensures proper handling of file types and formats for OpenAI’s API capabilities.

In the modal window, the second field shown, File Object, refers to the file the user will upload. Once the user selects the file and sets the purpose, clicking the Create button will trigger a process to handle the upload. This process will validate and prepare the file before sending it to OpenAI’s API for the specified purpose.

Let’s take a look at the procedure:

Copy to Clipboard

DECLARE
    -- Constants for REST API headers and values
    c_authorization_header CONSTANT VARCHAR2(50) := 'Authorization';        -- Header name for Authorization
    c_bearer_prefix        CONSTANT VARCHAR2(10) := 'Bearer';               -- Bearer prefix without space
    c_space                CONSTANT VARCHAR2(1)  := ' ';                    -- Space to be appended after Bearer
    c_content_type_header  CONSTANT VARCHAR2(50) := 'Content-Type';         -- Header name for Content-Type
    c_content_type_value   CONSTANT VARCHAR2(50) := 'multipart/form-data';  -- Value for multipart form-data content type
    -- Constants for API endpoint and parameters
    c_api_url              CONSTANT VARCHAR2(200) := 'https://api.openai.com/v1/files';  -- API endpoint URL
    c_http_method_post     CONSTANT VARCHAR2(10)  := 'POST';                            -- HTTP method POST
    c_file_param_name      CONSTANT VARCHAR2(20)  := 'file';                            -- Multipart parameter name for file
    c_purpose_param_name   CONSTANT VARCHAR2(20)  := 'purpose';                         -- Multipart parameter name for purpose
    -- Variables to store response and file-related data
    l_response        CLOB;                                -- Stores the response from the REST API
    l_multipart       APEX_WEB_SERVICE.t_multipart_parts;  -- Holds the multipart request parts
    l_body            BLOB;                                -- Generated request body in BLOB format
    l_blob            BLOB;                                -- Binary content of the uploaded file
    l_filename        VARCHAR2(4000);                      -- Filename of the uploaded file
    l_file_extension  VARCHAR2(4000);                      -- Extracted file extension
    l_is_valid        BOOLEAN;                             -- Indicates if the file validation succeeded
    -- Function to extract the file extension from the filename
    FUNCTION GET_FILE_EXTENSION (p_filename IN VARCHAR2) RETURN VARCHAR2 IS
        v_extension VARCHAR2(50);  -- Variable to store the extracted file extension
    BEGIN
        -- Check if the filename contains a dot (.)
        IF INSTR(p_filename, '.', -1) > 0 THEN
            -- Extract the file extension from the last dot onwards
            v_extension := SUBSTR(p_filename, INSTR(p_filename, '.', -1) + 1);
        ELSE
            -- Return NULL if no dot is found in the filename
            v_extension := NULL;
        END IF;
        RETURN v_extension; -- Return the extracted file extension
    END;
    -- Function to validate the file extension and its purpose
    FUNCTION VALIDATE_FILE_AND_PURPOSE (
        p_purpose        IN VARCHAR2,  -- Purpose of the uploaded file
        p_file_extension IN VARCHAR2   -- Extracted file extension
    ) RETURN BOOLEAN IS
    BEGIN
        -- Validate the purpose and file extension combinations
        IF p_purpose = 'fine-tune' AND p_file_extension != 'jsonl' THEN
            apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_INVALID_FILE_EXTENSION_FINE_TUNE'), p_display_location => apex_error.c_on_error_page);
            RETURN FALSE; -- Validation failed
        ELSIF p_purpose = 'batch' AND p_file_extension != 'jsonl' THEN
            apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_INVALID_FILE_EXTENSION_BATCH'), p_display_location => apex_error.c_on_error_page);
            RETURN FALSE; -- Validation failed
        ELSIF p_purpose = 'vision' AND p_file_extension = 'jsonl' THEN
            apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_INVALID_FILE_EXTENSION_VISION'), p_display_location => apex_error.c_on_error_page);
            RETURN FALSE; -- Validation failed
        ELSIF p_purpose = 'assistants' AND p_file_extension = 'jsonl' THEN
            apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_INVALID_FILE_EXTENSION_ASSISTANTS'), p_display_location => apex_error.c_on_error_page);
            RETURN FALSE; -- Validation failed
        END IF;
        RETURN TRUE; -- Validation passed
    END;
BEGIN
    -- 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;
    -- Fetch file details (filename and binary content) from APEX temporary files table
    BEGIN
        SELECT FILENAME, BLOB_CONTENT 
        INTO l_filename, l_blob 
        FROM APEX_APPLICATION_TEMP_FILES 
        WHERE NAME = :P20020_FILE_OBJECT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_FILE_NOT_FOUND'), p_display_location => apex_error.c_on_error_page);
            RETURN; -- Exit if file is not found
    END;
    -- Extract the file extension using the custom function
    l_file_extension := GET_FILE_EXTENSION(l_filename);
    -- Add an error if the file extension is NULL
    IF l_file_extension IS NULL THEN
        apex_error.add_error(p_message => APEX_LANG.MESSAGE('GIA_FILE_EXTENSION_MISSING'), p_display_location => apex_error.c_on_error_page);
        RETURN; -- Exit the process
    END IF;
    -- Validate the file extension and purpose using the custom function
    l_is_valid := VALIDATE_FILE_AND_PURPOSE(:P20020_PURPOSE, l_file_extension);
    -- Proceed only if the file validation is successful
    IF l_is_valid THEN
        -- Append the file data to the multipart request
        APEX_WEB_SERVICE.append_to_multipart(
            p_multipart    => l_multipart,
            p_name         => 'file',       -- Parameter name for the file
            p_filename     => l_filename,  -- Filename to be sent in the request
            p_body_blob    => l_blob        -- File content as BLOB
        );
        -- Append additional form data (purpose parameter) to the multipart request
        APEX_WEB_SERVICE.append_to_multipart(
            p_multipart    => l_multipart,
            p_name         => 'purpose',   -- Parameter name for the purpose
            p_body         => :P20020_PURPOSE -- Purpose value from the page item
        );
        -- Generate and send REST API request
        l_body := APEX_WEB_SERVICE.generate_request_body(p_multipart => l_multipart);
        -- Make the REST API call to upload the file
        l_response := APEX_WEB_SERVICE.make_rest_request(p_url => c_api_url, p_http_method => c_http_method_post, p_body_blob => l_body);
    END IF;
END;

If we divide the file upload process into smaller parts, each plays a critical role in ensuring smooth integration with OpenAI’s API. Let’s go through them step by step with the relevant code snippets:

1. File Retrieval

The first step is to retrieve the file from the APEX temporary storage table (APEX_APPLICATION_TEMP_FILES). This ensures that the file selected by the user exists and can be processed further.

Code Example:

Copy to Clipboard

The file’s name and content (FILENAME and BLOB_CONTENT) are retrieved using the unique file object identifier (:P20020_FILE_OBJECT).
If the file doesn’t exist, an error message (GIA_FILE_NOT_FOUND) is displayed, and the process terminates.

2. File Extension Extraction

Next, the filename is analyzed to extract its extension. This is critical for determining whether the file format is valid for the selected purpose.

Code Example:

Copy to Clipboard

The function GET_FILE_EXTENSION extracts the file extension by locating the last dot (.) in the filename.
If the filename doesn’t contain an extension, it returns NULL.

3. Validation

The extracted file extension is validated against the selected purpose to ensure compatibility. For example, files intended for fine-tune and batch must be in JSONL format.

Code Example:

Copy to Clipboard

The purpose (:P20020_PURPOSE) is checked against the file extension (l_file_extension) using specific rules.
If the validation fails, an error message is displayed, and the process stops.

4. Multipart Request Construction

After validation, the file data and its purpose are added to a multipart request. This ensures that the data is sent in the format expected by OpenAI’s API.

Code Example:

Copy to Clipboard

The APEX_WEB_SERVICE.append_to_multipart procedure is used to include both the file content (l_blob) and its purpose (:P20020_PURPOSE) in the request body.
Each piece of data is added with its respective name (file or purpose).

5. REST API Call

Once the multipart request is ready, it is sent to OpenAI’s /v1/files endpoint using a POST method. The response from the API is stored for further processing.

Code Example:

Copy to Clipboard

The APEX_WEB_SERVICE.generate_request_body procedure constructs the final request body from the multipart parts.
The APEX_WEB_SERVICE.make_rest_request procedure sends the request to OpenAI’s endpoint and stores the response in l_response.

6. Error Handling

If the API call fails or returns an error, appropriate feedback is provided to the user. Success responses display a confirmation message.

Code Example:

Copy to Clipboard

If l_response contains an error, the process stops, and an error message (GIA_UPLOAD_FAILED) is shown.
If successful, a confirmation message (GIA_FILE_UPLOAD_SUCCESS) is displayed.

With all this implemented, we can close the dialog once the procedure has been executed and capture the dialog’s closure to trigger a refresh of the list of items. This ensures that the Interactive Report is updated with the information of the new file that we have uploaded.

Thank you for reading Part 1. Stay tuned for Part 2, where we’ll dive even deeper into customizing the integration and further enhancing the file management process.

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