Output to External Web Service

With AOP, you have the flexibility to direct the output of document generation into a database table. When you press the button to call AOP, it creates your document and then sends it to an external REST Web Service. In our setup, we use a POST request configured through Oracle REST Data Services (ORDS). This REST Web Service processes the document and creates a new record in the database table

Step-by-Step Guide to ORDS Setup in Oracle APEX

1. Inside Oracle APEX, navigate to the SQL Workshop, and click on RESTful Services (ORDS).
   

• If your schema has not been registered, you can register it using the button below.

  

• Enable the ORDS Schema defaults with these settings.

  

• Create Module: In the RESTful Services section, you will see a list of options such as "Enabled Objects", "Modules", "Privileges", and "Roles". Click on “Modules” and click green creat button create a new one.

  

• To create a module in Oracle APEX's RESTful Data Services, enter the following details and click create Module button:

  • Name: Choose a unique name that identifies your module within the RESTful services.
  • Base Path: Set a base path, which serves as the root URL for all resources or services in this module.
  • Description (Optional): Provide a description to clarify the module's purpose.

  

• Configure Resource Template: After clicking Create Template button within module, Create a template by entering the following fields and pressing Create Template.

  

7. Within your ORDS module, choose the resource template to which you want to add a handler.
   This template represents the API endpoint you are configuring.

   • Click on the Create Handler button to add a new handler to the template module.

   • Specify Handler Details:

     • Method: Choose the HTTP method that this handler will respond to, such as GET, POST, PUT, or DELETE. This selection should align with the intended functionality of the handler, whether it's retrieving, creating, updating, or deleting data.

     • Source Type: PL/SQL
       To execute a block of PL/SQL code for operations that involve more complex logic, such as inserting or updating data.

     • Mime Types Allowed: If you select Method as POST, you will see the Mime Types Allowed option, which specifies the permissible MIME types for incoming requests. This is crucial for validating the type of data that the endpoint expects to receive.

     • Source Code: Input the code that will be executed when the handler is triggered. Ensure your code handles the specific tasks associated with the chosen HTTP method effectively.

       Click the Create Handler button to complete the ORDS creation process.

For example, you can use this sample code in the PL/SQL code area of your RESTful services. However, you first need to create a table to insert the AOP output file.

Table Example:

create table aop_output_file (
  id             number default aop_output_seq.nextval not null,
  file_size      number,
  output_file    blob,
  filename       varchar2(255),
  mime_type      varchar2(100),
  last_update_date date default sysdate,
  constraint aop_output_file_pk primary key (id)
);

PL/SQL code example:

DECLARE
   v_created_on    aop_output_file.last_update_date%TYPE;  
   v_mime_type     aop_output_file.mime_type%TYPE;
   v_file_data     BLOB;  
   v_file_size     aop_output_file.file_size%TYPE;
   v_access_token  VARCHAR2(100);
   v_file_name     VARCHAR2(255);
   v_file_id       NUMBER;
   v_content_disposition VARCHAR2(255);
         
   -- Custom exceptions
   INVALID_MIMETYPE       EXCEPTION;
   INVALID_ACCESS_TOKEN   EXCEPTION;

BEGIN
   -- Assign values from HTTP request headers and body to local variables
   v_mime_type    := :content_type;
   v_file_size    := :file_size;
   v_access_token := :access_token; 
   v_file_data    := :body;
   v_content_disposition := :content_disposition;

   -- Validating the access token.
   IF v_access_token != 'A456789' THEN
       RAISE INVALID_ACCESS_TOKEN;
   END IF;
   
   --extracting file_name from content disposition
   v_file_name    := replace(replace(v_content_disposition, 'attachment; filename="', ''),'"','');

   --insertion of file details into table
   SELECT SYSDATE INTO v_created_on FROM dual;
   INSERT INTO aop_output_file (id, filename, mime_type, output_file, file_size, last_update_date)
   VALUES (aop_output_seq.NEXTVAL, v_file_name, v_mime_type, v_file_data, v_file_size, v_created_on)
   RETURNING id INTO v_file_id;

   owa_util.status_line(201, '', false);
   owa_util.mime_header('application/txt', true);
   htp.prn('{"status": "File Successfully Inserted", "file_id": "' || v_file_id || '"}');

EXCEPTION
   WHEN INVALID_ACCESS_TOKEN THEN
       owa_util.status_line(401, 'Invalid Access Token', false);
       owa_util.mime_header('application/json', true);
       htp.prn('{"status": "Unauthorized", "message": "Inv
               alid access token provided."}');
   
   WHEN INVALID_MIMETYPE THEN
       owa_util.status_line(411, 'Invalid MIME TYPE', false);
       owa_util.mime_header('application/json', true);
       htp.prn('{"status": "Invalid MIME Type Uploaded", "message": "Only PDF files are allowed."}');

   WHEN OTHERS THEN
       owa_util.status_line(500, 'Internal Server Error', false);
       owa_util.mime_header('application/json', true);
       htp.prn('{"status": "Unknown Exception Error", "Error details": "' || SQLERRM || '"}');
END;

• Module Handler Parameters

  Module Handler parameters enable effective data exchange between APIs and applications. Below is a quick overview of each parameter's role and configuration:

• Name: Identifies the parameter as it appears in the URI or HTTP Header, used to map names incompatible with SQL.
• Bind Variable: Acts as the SQL identifier for the parameter. A NULL value indicates it is unbound and unused in SQL operations.
• Source Type: Indicates the parameter's origin—either HEADER, RESPONSE, or URI.
• Access Method: Describes the parameter's data flow direction with options IN for incoming, OUT for outgoing, and INOUT for both.
• Data Type: Specifies the type of data (not tied to Oracle types):
  • STRING: Any text length
  • INT, DOUBLE, LONG: Numeric values
  • BOOLEAN: True or False values
  • TIMESTAMP: Date and time values
• Comments: Provides additional information or notes about the parameter.

Parameter Setup: Within the chosen module, click on the 'Add Row' button to include new parameters. Configure each parameter by filling in the attributes as outlined above.

Oracle APEX Setup for Directing AOP Outputs to Database Storage

For setting up Oracle APEX, you can refer to other topic-specific documentation for templates, source code, and additional information. After that, simply insert this code into the Init PL/SQL Code section of Oracle APEX.

aop_api_pkg.g_web_service_url := 'your restful service url';
aop_api_pkg.g_web_service_headers := '{"access_token": "your access token"}';