Quick Install Guide

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

Unzip the file you downloaded from https://www.united-codes.com/products/apexmessageservice/
Install the PL/SQL API.
- Go in APEX to SQL Workshop > SQL Scripts > Upload
- Select file ddl_uc_ams_manual_install.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.
Import new versions of the plug-ins.
- Go in APEX to App Builder > Your application > Shared Components > Plug-ins > Import
- Be sure to select the directory corresponding to the version of Oracle APEX you are using - NOT the version of AMS.
- Select file /plugin/process_type_plugin_unitedcodes_ams_process.sql (for the AMS Process type plug-in)
- Select file /plugin/dynamic_action_plugin_unitedcodes_ams_da.sql.sql (for the AMS Dynamic Action type plug-in)
- Enter the details of the AMS Server or, in case the AMS Cloud is used, the API key. If using the AMS cloud version, ensure the API Key is set for each plug-in.
  An API Key is not necessary for on-premises installations.

That is it! Add the "APEX Message Service" Dynamic Action or Process to the page you want to use AMS.

Optional you can also install the sample application

Install the AMS Sample Applications.

Import the AMS Sample Apps you find in /app
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 AMS Server

Depending on 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:

You may need to install the SSL certificate in your Oracle Wallet to connect to the AMS Cloud over HTTPS. The Oracle Cloud has our certificate loaded by default. Here are the commands to install our certificate in your own Oracle Wallet. First, make sure to copy the file /apex_version/db/AmazonRootCA.cer to your database server, e.g., in the directory /opt/oracle/admin/XE/.

orapki wallet create -wallet /opt/oracle/admin/XE/ -pwd YourWalletPasswd -auto_login
orapki wallet add -wallet /opt/oracle/admin/XE/ -trusted_cert -cert "/opt/oracle/admin/XE/AmazonRootCA.cer" -pwd YourWalletPasswd

To test the connection manually, you can do this in SQL:

select apex_web_service.make_rest_request(
        p_url         => 'https://api.ams.united-codes.com',
        p_http_method => 'GET',
        p_wallet_path => 'file:/opt/oracle/admin/XE/',
        p_wallet_pwd  => 'YourWalletPasswd') as r
  from dual

Connection issues to the AMS Server or AMS Cloud are primarily due to an invalid TNS, ACL, or Wallet configuration. 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.

Possible issues you might encounter

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL)

Make sure the APEX schema has the rights to connect to the AMS Server (http(s)://api.ams.united-codes.com for the Cloud version or your local URL in case of the on-premises version).

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

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_230100',
                           principal_type => xs_acl.ptype_db));
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 are 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 the database calls a local entry (which doesn't need a certificate), and the webserver redirects to handle the https call.

Here's an example when in the plug-in, you would specify a local address: http://united-codes.localdomain/ams/

<VirtualHost *:80>
ServerName  united-codes.localdomain
ServerAlias united-codes.localdomain
RewriteEngine On
ProxyVia On
ProxyRequests Off
SSLProxyEngine On
ProxyPass        /ams/   https://api.ams.united-codes.com/
ProxyPassReverse /ams/   https://api.ams.united-codes.com/
</VirtualHost>

PLS-00753: malformed or corrupted wrapped unit

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

APEX Debugging

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

info

See this video on how to set LEVEL6 debugging.