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

  3. 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.

  • 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>

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.