Skip to main content

Quick Install Guide

Quick Install Guide​

This guide will install the PL/SQL packages and the Oracle APEX plug-ins for AOP​

  1. Unzip the file you downloaded from https://www.apexofficeprint.com

  2. Install the PL/SQL API.

    • Go in APEX to SQL Workshop > SQL Scripts > Upload
    • Select file /db/aop_db_pkg.sql
    • Give it a name and click the Upload button.
    • Click the run icon for the script you just uploaded, confirm that the schema in the upper right-hand corner of the screen is correct and click Run Now.
    • All statements should be successful.

Repeat the above for /db/aop_modal_pkg.sql

  1. Import new versions of the plug-ins.

    • Go in APEX to App Builder > Your application > Shared Components > Plug-ins > Import
    • Select file /plugin/process_type_plugin_be_apexrnd_aop.sql (for the AOP Process type plug-in)
    • Select file /plugin/dynamic_action_plugin_be_apexrnd_aop_da.sql (for the AOP Dynamic Action type plug-in)
    • Select file /plugin/dynamic_action_plugin_be_apexrnd_aop_convert_da.sql (for the Convert Dynamic Action type plug-in)
    • Select file /plugin/dynamic_action_plugin_be_apexrnd_aop_modal.sql (for the Modal Dynamic Action type plug-in)
    • Enter the details of the AOP Server or in case the AOP Cloud is used the API key. If using the cloud version of AOP, make sure the API Key is set for each plug-in.
      An API Key is not necessary for on-premises installations.

That is it! On the page you want to use AOP, add the "APEX Office Print" Dynamic Action or Process.

Optional you can also install the sample application​

Install the AOP Sample Applications.

  • Import the AOP Sample Apps you find in /app
  • You can import them in any order.
  • Make sure that you install the Supporting Objects. This will install the necessary database objects to run the sample applications.

Testing the connection from PL/SQL to the AOP Server​

Depending your environment, only HTTPS connections might be allowed. This is the case for the Oracle Cloud for example.

You want to check your connection by running the following in SQL Workshop > SQL Commands:

Using SQL PL/SQL with aop_api_pkg package methods and apex_web_service.make_rest_request​

  • AOP API HTTP and using aop_api_pkg.is_aop_accessible
begin
  -- in case of an on-premises AOP Server, change the URL
  aop_api_pkg.is_aop_accessible(
    p_url            => 'http://api.apexofficeprint.com',
    p_proxy_override => null,
    p_wallet_path    => null,
    p_wallet_pwd     => null);
end;
-- should return `true`
  • AOP API with HTTP and using aop_api_pkg.send_aop_sample
begin
  -- in case of an on-premises AOP Server, change the URL
  aop_api_pkg.send_aop_sample(
    p_url            => 'http://api.apexofficeprint.com',
    p_proxy_override => null,
    p_wallet_path    => null,
    p_api_key        => '<your_api_key>',
    p_wallet_pwd     => null);
end;
-- You should see Statement processed.
  • AOP API with HTTPS and using aop_api_pkg.send_aop_sample
begin
  -- in case of an on-premises AOP Server, change the URL
  aop_api_pkg.send_aop_sample(
    p_url            => 'https://api.apexofficeprint.com',
    p_proxy_override => null,
    p_wallet_path    => null,
    p_api_key        => '<your_api_key>',
    p_wallet_pwd     => null);
end;
-- You should see Statement processed.
  • AOP API HTTPs and using aop_api_pkg.is_aop_accessible
begin
  -- in case of an on-premises AOP Server, change the URL
  aop_api_pkg.is_aop_accessible(
    p_url            => 'https://api.apexofficeprint.com',
    p_proxy_override => null,
    p_wallet_path    => null,
    p_wallet_pwd     => null);
end;
-- should return `true`
  • using apex_web_service.make_rest_request
DECLARE
  l_response CLOB;
BEGIN
  SELECT apex_web_service.make_rest_request('http://api.apexofficeprint.com/marco', 'GET') INTO l_response FROM dual;
  dbms_output.put_line(l_response);
END;
-- should return `polo`

Using curl in terminal​

You can also test connectivity with curl:

curl http://api.apexofficeprint.com/marco

This should return a polo response.

Access Control List (ACL) Error

If you encounter the error network access denied by ACL, as shown in the image, it indicates that Oracle blocks outgoing RESTful requests by default.

This error typically occurs when you attempt to run this command for the first time.
In order to resolve this issue, you need to configure the Access Control List (ACL).
Solution: Configuring the Access Control List(ACL)

Securing API Communication with HTTPS.​

Execute the following code to test the https connection:

DECLARE
  l_response CLOB;
BEGIN
  SELECT apex_web_service.make_rest_request('https://api.apexofficeprint.com/marco', 'GET') INTO l_response FROM dual;
  dbms_output.put_line(l_response);
END;

This should return the polo response without an error.
If you receive a certificate validation error, it means the Oracle database cannot verify the AOP server’s SSL certificate.

In order to connect to the AOP Cloud over HTTPS, you may need to install the SSL certificate in your Oracle Wallet. The _Oracle Cloud _has our certificate loaded by default.
Here are the steps and necessary commands to install our certificate in your own Oracle Wallet.

1. Obtain the certificate:​

Here are the different ways to get the certificate if you require a manual configuration.

1.1 . Get certificate from browser (Exmple for Google Chrome).​
  • In the browser, go to https://api.apexofficeprint.com.
  • On the left side of the URL, click on the lock button.
  • Go to Connection is secure > Certificate is valid > Details and export it.
2.2 . Alternatively, use OpenSSL to retrieve it.​

Script to get all the ssl certificates:

openssl s_client -showcerts -verify 5 -connect api.apexofficeprint.com:443 < /dev/null |
   awk '/BEGIN CERTIFICATE/,/END CERTIFICATE/{ if(/BEGIN CERTIFICATE/){a++}; out="cert"a".pem"; print >out}'

2. Store the certificates in a designated directory and rename them appropriately (e.g., root certificate as amazon_root_ce_1.pem).​

Renaming them according to the CA:

 for cert in *.pem; do
   newname=$(openssl x509 -noout -subject -in $cert | sed -nE 's/.*CN ?= ?(.*)/\1/; s/[ ,.*]/_/g; s/__/_/g; s/_-_/-/; s/^_//g;p' | tr '[:upper:]' '[:lower:]').pem
   mv $cert $newname
 done

3. Oracle Wallet Setup​

Create an Oracle Wallet and import the root certificate. Verify the wallet contents to ensure the root certificate is included in the Trusted Certificates.

  • Wallet Configuration:
orapki wallet create -wallet /tmp/.. -pwd oracle123 -auto_login
# or
# orapki wallet create -wallet /opt/oracle/admin/XE/ -pwd YourWalletPasswd -auto_login
  • Add certificate to wallet
orapki wallet add -wallet ../../oracle_wallet/w1.wallet/ -cert ./cert1.pem -trusted_cert -pwd oracle123
# or
# orapki wallet add -wallet /opt/oracle/admin/XE/ -trusted_cert -cert "/opt/oracle/admin/XE/AmazonRootCA.cer" -pwd YourWalletPasswd
  • Display the wallet info:
orapki wallet display -wallet ../../oracle_wallet/w1.wallet/ -pwd oracle123

  • Set the wallet path in Oracle APEX’s instance settings to complete the configuration.

  • Give the location file://home/oracle/ssl as shown in the image, on the oracle wallet on your instance.

Issue in the path resolves to an error ORA-29273 HTTP Request failed ORA-28759 failure to open file

  • Re-run the HTTPS connectivity code.
DECLARE
  l_response CLOB;
BEGIN
  SELECT apex_web_service.make_rest_request('https://api.apexofficeprint.com/marco', 'GET') INTO l_response FROM dual;
  dbms_output.put_line(l_response);
END;

You should receive the polo response, confirming secure communication.

Complete Startup Guide

You can get detailed information from our YouTube video.

Connection issues to the AOP Server or AOP Cloud are most of the time due to an invalid configuration of the TNS, ACL or Wallet. If a call to apex_web_service works in SQL, but still not in your Oracle APEX application, it's most likely that the wallet path and password are not specified in the INTERNAL workspace of your Oracle APEX environment.

Runtime-only environment, install from SQL command line​

To install the AOP packages in a runtime-only Oracle APEX environment, connect with the Oracle database user/schema where you want to deploy AOP e.g. HR.

Run two files:

@/db/aop_db_pkg.sql
@/db/aop_modal_pkg.sql

This will create the AOP PL/SQL API that the Oracle APEX Plug-ins use. The plug-ins are part of your Oracle APEX application and don't need to imported manually.

Possible issues you might encounter​

Configuring the Access Control List (ACL)​

For the error,

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL)
  • Ensure you have DBA access to configure ACL settings.
  • Use the following code to add the AOP API host api.apexofficeprint.com to the ACL with your APEX principal name. Since this documentation uses Apex version 23.1, APEX_230100 is the correct principal name for this version. If you are using a different Apex environment, please refer to the documentation for the appropriate code to execute

Make sure the APEX schema has the rights to connect to the AOP Server (http(s)://www.apexofficeprint.com for the Cloud version or your local URL in case of the on-premises version).

Here's an example how to allow the APEX_230100 schema to connect to any URL:

BEGIN
    dbms_network_acl_admin.Append_host_ace(host => 'api.apexofficeprint.com',
    ace => Xs$ace_type(privilege_list => Xs$name_list('connect'),
                  principal_name => 'APEX_230100'
           , principal_type => xs_acl.ptype_db));
END; 
  -- see all ACLS
SELECT principal,
       host,
       lower_port,
       upper_port,
       acl,
       'connect'          AS PRIVILEGE,
       Decode(dbms_network_acl_admin.Check_privilege_aclid(aclid, principal,
              'connect')
       , 1, 'GRANTED',
       0, 'DENIED', NULL) PRIVILEGE_STATUS
FROM   dba_network_acls
       join dba_network_acl_privileges USING (acl, aclid)
UNION ALL
SELECT principal,
       host,
       NULL               lower_port,
       NULL               upper_port,
       acl,
       'resolve'          AS PRIVILEGE,
       Decode(dbms_network_acl_admin.Check_privilege_aclid(aclid, principal,
              'resolve')
       , 1, 'GRANTED',
       0, 'DENIED', NULL) PRIVILEGE_STATUS
FROM   dba_network_acls
       join dba_network_acl_privileges USING (acl, aclid) 
-- drop ACLs
BEGIN
    dbms_network_acl_admin.Drop_acl(acl =>
    'NETWORK_ACL_0840B846F825036BE063020012AC3B8E');
EXCEPTION
    WHEN OTHERS THEN
      NULL; -- Ignore errors if ACL doesn't exist
END;

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-29024: Certificate validation failure​

This means you are trying to access an HTTPS URL, but the certificate is not loaded correctly in the Oracle wallet.

There're two ways to get around this:

  • Option 1: You can load our certificates in your Oracle wallet (the Oracle DB is doing the call to our server). See above for instructions using orapki.
  • Option 2: You can add an entry in your webserver so a local entry is called by the database (which doesn't need a certificate) and the webserver is doing the redirect to handle the https call.

Here's an example when in the plug-in you would specify a local address: http://apexrnd.localdomain/aop/

<VirtualHost *:80>
ServerName  apexrnd.localdomain
ServerAlias apexrnd.localdomain
RewriteEngine On
ProxyVia On
ProxyRequests Off
SSLProxyEngine On
ProxyPass        /aop/   https://api.apexofficeprint.com/
ProxyPassReverse /aop/   https://api.apexofficeprint.com/
</VirtualHost>

ORA-29273 HTTP Request failed ORA-28759 failure to open file​

The error is due to wrong wallet file (or SSL file) or wrong DIRECTORY object / path. The message indicates that the database was not able to open the wallet file, at the location you specified in the p_wallet_path parameter or in APEX Instance Setting > Wallet > Wallet Path.

Path
  • when specifying the location, make sure to use a complete URI, i.e. file:///path/to_file (with file:// being the protocol, followed by the path specification.
  • make sure that the path is correct, and points to a directory containing the ewallet.p12 and (optionally) the cwallet.sso files.
  • make sure that the operating system privileges allow the database to access the directory, and to open the file. The OS user which executes the Oracle database processes must be able to access the files.

PLS-00753: malformed or corrupted wrapped unit​

There might be multiple reasons why you get this. There might be a permission issue, most likely on UTL_FILE. Another reason might be that during uploading of the script in SQL Workshop > SQL Scripts the File Character Set was not set to Unicode UTF-8.

ORA-20000: The AOP Server can not process your request. RequestID: xxxx-xxxxx Error while generating file. Please contact AOP for further inquiry​

This error means the AOP Server has difficulties full-filling your request. In this case it's best to debug the request to the AOP Server or AOP Cloud. You can get the debug JSON by inserting the following PL/SQL code in the Init PL/SQL section of the Dynamic Action or before you call the AOP PL/SQL API:

aop_api_pkg.g_debug := 'Local';

The next time you print, you should get a JSON. Once you have the local JSON file, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.

APEX Debugging​

When you run Oracle APEX in debug mode you can see how long a request took. In case you want to see more detailed debug information, run in LEVEL6 (=Application Trace) debug mode.

info

See this video how to set LEVEL6 debug on.

Configuring HTTPS access on RDS (Wallet & ACL Setup)​

This guide explains how to configure Oracle Wallet and ACL permissions on Amazon RDS so that AOP can securely call HTTPS endpoints without errors such as ORA-29024 or ORA-24247. Since Amazon RDS does not allow OS-level access, the wallet must be created externally and uploaded to an RDS directory (typically via S3 integration), or you may use the preconfigured wallet provided in our S3 bucket, which has public access and is available over HTTP.

For additional details and best practices, refer to the official AWS documentation below (client certificate–related steps are not required for our implementation): https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Concepts.ONA.html

After connecting to your Amazon RDS instance, proceed with the following steps.

Step 1 — Create Wallet Directory in RDS​

EXEC rdsadmin.rdsadmin_util.create_directory('WALLET_DIR');

This creates a logical directory in Oracle RDS.

Step 2 — Download Wallet Files into RDS because RDS has no OS access, use UTL_HTTP to download wallet files.​

Example for cwallet.sso

DECLARE
  l_url            VARCHAR2(1000) := 'http://apexofficeprint.s3.eu-west-1.amazonaws.com/rdswallet/cwallet.sso';
  l_http_request   UTL_HTTP.REQ;
  l_http_response  UTL_HTTP.RESP;
  l_file_handle    UTL_FILE.FILE_TYPE;
  l_raw_buffer     RAW(32767);
  l_wallet_dir     VARCHAR2(30) := 'WALLET_DIR';
  l_filename       VARCHAR2(50) := 'cwallet.sso';
BEGIN
  -- 1. Initialize Request
  l_http_request := UTL_HTTP.BEGIN_REQUEST(l_url, 'GET');
  l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request);


  -- 2. Open File in Write Binary Mode ('wb')
  l_file_handle := UTL_FILE.FOPEN(l_wallet_dir, l_filename, 'wb', 32767);


  -- 3. Stream binary data from URL to File
  BEGIN
    LOOP
      -- Read raw bytes into buffer
      UTL_HTTP.READ_RAW(l_http_response, l_raw_buffer, 32767);
      -- Write raw bytes to the local file
      UTL_FILE.PUT_RAW(l_file_handle, l_raw_buffer, TRUE);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      -- Normal exit: end of file reached
      NULL;
  END;


  -- 4. Clean up
  UTL_FILE.FCLOSE(l_file_handle);
  UTL_HTTP.END_RESPONSE(l_http_response);
  
  DBMS_OUTPUT.PUT_LINE('File ' || l_filename || ' successfully downloaded to ' || l_wallet_dir);


EXCEPTION
  WHEN OTHERS THEN
    -- Ensure handles are closed even on failure
    IF UTL_FILE.IS_OPEN(l_file_handle) THEN
      UTL_FILE.FCLOSE(l_file_handle);
    END IF;
    UTL_HTTP.END_RESPONSE(l_http_response);
    RAISE;
END;
/

Repeat the same step for cwallet.p12 by updating only the URL and filename.

Step 3 — Verify Wallet Files​

SELECT * 
FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'WALLET_DIR'));

You should see: cwallet.sso and cwallet.p12

Step 4 — Find the Actual Wallet Path​

SELECT directory_path
FROM all_directories
WHERE directory_name = 'WALLET_DIR';

Example output: /rdsdbdata/userdirs/01

Step 5 — Set this directory as your wallet path and confirm you receive a polo response from the API.​

DECLARE
  req       UTL_HTTP.REQ;
  res       UTL_HTTP.RESP;
  url       VARCHAR2(4000) := 'https://api.apexofficeprint.com/marco';
  buffer    VARCHAR2(32767);
BEGIN
  -- 1. Start the Request
  UTL_HTTP.SET_WALLET('file://rdsdbdata/userdirs/01');
  req := UTL_HTTP.BEGIN_REQUEST(url, 'GET');
  
  -- 2. (Optional) Set Headers if needed
  -- UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/5.0');

  -- 3. Get the Response
  res := UTL_HTTP.GET_RESPONSE(req);

  -- 4. Loop through the response body and print it
  BEGIN
    LOOP
      UTL_HTTP.READ_TEXT(res, buffer, 32767);
      DBMS_OUTPUT.PUT_LINE(buffer);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE(res);
  END;

EXCEPTION
  WHEN OTHERS THEN
    UTL_HTTP.END_RESPONSE(res);
    RAISE;
END;
/

Expected response: polo

note

Please note that the wallet in the URL contains the loaded ​Amazon root certificate that our main api usages. The password for the wallet is just P12PASSWORD as mentioned on the Amazon's RDS guide.