Cloning a PDB with APEX pointing to ORDS REST services on the same database

This post shows cloning a database with APEX and some database REST services in the same database, which are accessed from APEX via a REST data source. Say we have a PDB testpdb and clone it to clonepdb for dev/test. The EMPLOYEES table in HR is REST enabled and the APEX application has a REST Data Source for that REST service. Without any changes, the REST Data Source from clonepdb would still point to testpdb. But it should point to clonepdb. This could be changed manually, but if this is needed for automatic provisioning of developer environments, some automation would be helpfull.

The URL is shown in ORDS Database Actions under REST/AutoREST

The APEX application has a REST Data Source TestEmployees pointing to that service:

The Remote Server attribute contains the IP address and the database connection, in this case the TESTPDB. Click on the pen next to it.

Enable Prompt on Install.

Now clone testpdb to clonepdb:

create pluggable database clonepdb from testpdb 
file_name_convert = ('/testpdb/', '/clonepdb/');

alter pluggable database clonepdb open;

For easier access add clonepdb to tnsnames.ora:

cat >> $TNS_ADMIN/tnsnames.ora <<EOF
CLONEPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonepdb)
    )
  )

EOF
tnsping clonepdb

Stop ORDS, register clonepdb with ORDS interactively or create a password.txt file with your passwords and run:

cat > password.txt <<EOF 
<password>
<password>
EOF

ords install --db-pool clonepdb --db-hostname localhost --db-port 1521 --db-servicename clonepdb --feature-sdw true --feature-db-api true --feature-rest-enabled-sql true --admin-user sys --proxy-user --password-stdin < password.txt

Restart ORDS and navigate to REST/AutoREST. The service for the employees table also exists in clonepdb ...

... and works as expected.

The APEX application also made it into clonepdb, but of course the TestEmployees REST Data Source still points to testpdb

As the application has been cloned with the PDB, the Prompt on Install attribute does not trigger. We need to ex- and import the application. Export the application from testpdb and make sure that Export with Original IDs is checked

Import the application on clonepdb and check Reuse Application ID 100 From Export File 

In the last step, the installer prompts for the Base URL of the Remote Server, which has been tagged for Prompt on Install. Change it from testpdb to clonepdb.

Open the REST Data Source, it now shows the correct local endpoint. 

But for automatic deployment of developer environments, we need to get rid of the interactive step. Before we can import the export file by running it in SQLcl, we need to set the override with APEX_APPLICATION_INSTALL.SET_REMOTE_SERVER .

declare
    l_base_url varchar2(255);
begin
  apex_application_install.set_remote_server(
    p_static_id => '192_168_56_104_ords_testpdb',
    p_base_url  => 'http://192.168.56.104:8080/ords/clonepdb/');

    l_base_url := apex_application_install.get_remote_server_base_url('192_168_56_104_ords_testpdb');
    DBMS_OUTPUT.PUT_LINE('Base URL for ' || '192_168_56_104_ords_testpdb' || ' is now ' || l_base_url);
end;
/

That should give

And opening the REST Data Source in the APEX application on clonepdb should show the URL pointing to clonepdb.

So all that is needed after cloning the database with the APEX application and the REST service is running a script with apex_application_install.set_remote_server and re-importing the application.