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.

Oracle Cloud: create Storage Containers via REST API

For this Oracle Cloud tutorial, two Storage Containers in the Oracle Storage Cloud Service are required. The Oracle Documentation describes, how this can be done via REST-Api and wget, see 'Authenticating Access When Using the REST API'. Two alternatives are described here: use the RESTClient Firefox-Extension or the Windows-Tool CloudBerry Explorer for OpenStack.

After login to the Oracle Cloud Services, start at the Dashboard where all subscribed services are listed.

Click on the Oracle Storage Cloud Service to get to the details.

Copy the URL of the REST endpoint shown here for further usage. The first call will be the authentication. The URL has to be modified for that as described in the documentation. For example, starting with

https://gse0nnnnnnn.storage.oraclecloud.com/v1/Storage-gse0nnnnnnn

remove everything from v1 on, so you get

https://gse0nnnnnnn.storage.oraclecloud.com/

Then append auth/v1.0 so the result looks like:

https://gse0nnnnnnn.storage.oraclecloud.com/auth/v1.0

Now open the Firefox browser, install and open the RESTClient Extension (or any other browser/extension combination that will do the trick). Create a GET request and paste the URL modified above. Before it can be called, it needs the information about username and password in a header each.

So click on Headers/Custom Header to create a header.

The first header gets the name X-Storage-User. In the value field, enter the username in the format 'Storage-'<identity domain>':'<username> eg.

Storage-asdf1234:john.dunbar

Save it as a favorite in RESTClient, we will need it several times. Then create a second header with the name 'X-Storage-Pass' and the users password as value.

The result should look as shown above. The URL with auth/v1.0 attached and both custom headers. Continue with the 'Send' button.

An authentication token will be returned, which needs to be copied for further usage.

To create a new container, simply write REST conform to a non-existent resource(= storage container). As this resource does not exist, a new one will be created. The name of the resource is the base URL, which we copied from above, and the name of the storage container attached (dbBackupContainer in this case).
It is important to remove both headers created above (also for the following requests). Instead create a new X-Auth-Token header. Use the authentication token from above as value, then click on 'Send'.

If everything is OK, the request will return with '201 Created'.

The REST-API can be used to query all existing storage containers in that instance. Just leave the X-Auth-Token header as it is, also for any further requests. In case of an 'unauthorized' error, probably the token has timed out, so just repeat the first step.
The URL for the query is:
https://gse0nnnnnn.storage.oraclecloud.com/v1/Storage-gse0nnnnnn?limit=15

In return comes a list of all existing storage containers, including the just created dbBackupContainer.

Additional to the REST-API, the Oracle Storage Cloud Service also speaks the OpenStack Swift API. So in the next part if this tutorial, the management of storage containers via OpenStack will be shown with the tool CloudBerry Explorer.

==> Storage Container verwalten per CloudBerry Explorer

SOA-Suite 12c: configure inbound REST adapter

Since release 12c, the Oracle SOA Suite comes with an adapter for REST-Services. This tutorial shows how to configure an inbound REST Adapter for XML and JSON Payload.

Start with a new SOA Application, choose 'Empty Composite'.

Create a new REST adapter on the left side of the composite at the Exposed Services.

A Resource Path could be defined, this simple example work without one. At least one HTTP operation needs to be bound by clicking the green plus sign (+) under 'Operation Bindings'. The last two entries are presets for easier setup, but leaving out some options. So we start with 'Add operations binding'

Check JSON and XML as payload and POST for the HTTP Verb. For defining the XML-Schema, click the cog reading 'Define Schema for Native Format'.

Click through the Native Format Builder with 'Next' ...

... and 'Next' ... 

... und 'Next' ... 

... until the page, where a sample JSON file could  be uploaded via the 'Browse' button. If no file is available, just enter the desired JSON Data in the Sample text field, for example 

{

"Event" : "asdf",

"Payload" : "dfgh"

}

and click next.

The JDeveloper comes back with a ready-to-use Schema, which will be used later. Click through the wizard with 'Next' and 'Finish'

For the Response tab, also choose XML and JSON for Payload. This isn't necessary for a POST operation, but useful for testing. Select the newly created XML Schema by clicking on the magnifier icon next to 'Schema URL'. Close both dialogs with 'OK'.

The REST service is now completed. Next a BPEL process is needed, e.g. by right mouse button and choosing Insert|BPEL Process.

Change the template to 'Synchronous BPEL Process' and uncheck 'Expose as a SOAP service'. Leave the rest and finish with 'OK'.

The interface of the BPEL process does not fit to our REST service and needs to be removed. Click the symbol of the inbound interface so that the surrounding circles is represented by a dotted line. Press the 'DELETE' key on your keyboard and answer both questions with 'YES'.

Now the RestService can be wired to the BPEL process. Click 'OK' when asked for a transaction. 

Open the BPEL Process. The input and output variables for the old interface are still there. They need to be removed by clicking on the variables button (x), mark both and click on delete.

Switch to source view. The old WSDL is still imported, so the line reading <import ui:processWSDL... needs to be deleted.

The old interface is deleted, so the receiveInput can now be wired to the RestService.

Change the Port Type to the new RestService_ptt.

A new input variable is needed. Click on 'Auto-Create Variable and in the 'Create Variable' dialog just click 'OK'. 

Wire the replyOutput also to the RestService and repeat the steps above.

To let the process do at least anything, create a file adapter to write down the messages.

Click through the wizard. Choose 'Write File' for Operation Type. Check 'Add Output Header' for more verbose output.

Choose an output directory, then a filename template and append a sequence (%SEQ%).

Select the Root-Element of the above created Schema.

Click through the wizard to the end.

Now put an Invoke activity right behind receiveInput and wire it to fileOutput.

Create a new input and output variable via the green plus (+) and close the dialog.

Put an assign activity in front of the Invoke1 activity.

Open the assign activity and map the Root-Element of receiveInput...InputVariable to Invoke1_Write_InputVariable.

For the return, place another assign activity between Invoke1 and ReplyOutput and create the mapping accordingly for Invoke1_Write_OutputVariable.

That's it, die example is ready to run. Deploy it to a SOA Suite instance of your choice and open

FMW Control.

In FMW Control, select the project, click the test button and choose the RestService.

Change both Media Types to application/json. Switch to Raw View, enter your request and test the service.

If nothing went wrong, the event should be logged to the file and the response gives back the folder and the filename.

At the deployment, the SOA Suite automatically creates an according WADL. This could be copied from the service test page for testing the service in JDeveloper.

Open the HTTP Analyzer via Tools|HTTP Analyzer and click on the 'Open URL...' icon.

Paste the copied WADL-URL and hit RETURN.

When JDeveloper parsed the WADL, the test client can be called via the 'Test' button.

The test form seems to have still some problems with JSON. So switch the Content-Type to application/xml and test it via 'Send Request'. Becase the returning data is now 'fileName' and 'directory', instead of expected 'Event' and 'Payload', the JDev cannot parse the returning data. But for a clean solution, this example would have become even longer.

Instead, simply switch to Raw Message view to see that the expected data is returned.

As this simple example shows, the REST/JSON-support in JDeveloper and SOA Suite is definitively ready to use.