Oracle Document Generator

How to implement in an APEX application

Configure Document Generator: 
Follow Oracle's documentation to set up the Oracle Document Generator pre-built function 
in your OCI tenancy and configure your APEX instance to use it.
Create a Page Process: 
In your APEX application, create a new Page Process that runs when a "Print" button is clicked.
Use the PL/SQL: 
Copy and paste the PL/SQL code from Step 2 into this Page Process.
Reference the Template: 
Modify the PL/SQL code to retrieve your invoice_template.docx from 
APEX_APPLICATION_TEMP_FILES (assuming you've provided a file upload item on your page) 
or APEX_APPLICATION_STATIC_FILES. 

Example:

Step 1: 
Create the Word (invoice_template.docx) template that includes placeholders for your data. 
The placeholders are specified using the format ${DATA_TAG}. 

**Invoice**

Invoice Number: ${invoice_number}
Date: ${invoice_date}

Dear ${customer.name},

Thank you for your business.

**Order Details**
${items_loop}
- Product: ${product_name}
- Quantity: ${quantity}
- Price: ${price}
${/items_loop}

**Total: ${total_amount}**

Step 2: Write the PL/SQL code
This PL/SQL block generates a DOCX file using the template and data. 
You can place this code in an APEX process or a custom package. 

DECLARE
    l_template_blob     BLOB;
    l_data_clob         CLOB;
    l_document_blob     BLOB;
    l_filename          VARCHAR2(255);

BEGIN
    -- 1. Get the template from an APEX collection or file.
    
    -- This example assumes the DOCX template is loaded into a BLOB variable.
    -- For example, you can get it from APEX_APPLICATION_TEMP_FILES.
    -- For simplicity, this example assumes you have it ready.
    -- In a real application, you might use apex_print.upload_template.
    
    SELECT blob_content INTO l_template_blob FROM apex_application_temp_files 
    WHERE name = 'invoice_template.docx';

    -- 2. Prepare the data as a JSON CLOB.
    l_data_clob :=
        sys.json_object_t(
            'invoice_number', 1001,
            'invoice_date', TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
            'customer', sys.json_object_t(
                'name', 'Jane Doe'
            ),
            'items_loop', sys.json_array_t(
                sys.json_object_t(
                    'product_name', 'Product A',
                    'quantity', 2,
                    'price', 15.00
                ),
                sys.json_object_t(
                    'product_name', 'Product B',
                    'quantity', 1,
                    'price', 25.00
                )
            ),
            'total_amount', 55.00
        ).to_clob;

    -- 3. Generate the document using the Oracle Document Generator.
    l_document_blob := apex_print.generate_document(
        p_data          => l_data_clob,
        p_template      => l_template_blob,
        p_template_type => 'DOCX' -- apex_print.c_template_docx,
        p_output_type   => apex_print.c_output_docx -- Request DOCX output
    );

    -- 4. Set the headers for the file download.
    l_filename := 'invoice_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.docx';
    
    -- Ensure you are in a valid APEX session with download privileges
    sys.owa_util.mime_header('application/vnd.openxmlformats-officedocument.wordprocessingml.document', false);
    sys.htp.p('Content-Length: ' || DBMS_LOB.getlength(l_document_blob));
    sys.htp.p('Content-Disposition: attachment; filename="' || l_filename || '"');
    sys.owa_util.http_header_close;
    
    -- 5. Download the generated document.
    sys.wpg_docload.download_file(l_document_blob);

END;