8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Configure Multiple Databases

If you have used mod_plsql in the past, you will know that each Database Access Descriptor (DAD) can make a connection to a specific user@database combination, allowing a single Oracle HTTP Server to service requests for multiple schemas in multiple databases. Oracle REST Data Services (ORDS) is similar in this respect.

Related articles.

Background and Assumptions

When you install ORDS, you are prompted for database credentials, which are used to configure a default database connection called "apex", which is associated with the default URL (http(s)://server:port/ords/). If your ORDS installation is only used for a single purpose, like deploying APEX from a single database, then you don't need to worry about adding new database connections.

If you require any of the following from a single ORDS installation, you will need to create additional database connections and URL mappings.

For a multi-purpose ORDS installation, it would be sensible to forget the existence of the default database connections as the associated URL can be a little confusing without an additional qualifier in the path.

For each database connection created, you need a URL mapping, to link a specific path or URL to the database. This mapping can take one of two forms.

The examples in this article will use the request path prefix approach.

This article assumes the following.

Create a New Database Connection for APEX

The following example creates a new database connection called "pdb1", which supports APEX, APEX RESTful Services and Oracle REST Data Services.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Jun 17, 2016 7:02:04 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: pdb1, pdb1_pu, pdb1_al, pdb1_rt
Jun 17, 2016 7:02:04 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.5.124.10.54 is installed.
$

The main steps here are as follows.

With the new database connection in place, you need to map a URL to point to it.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Jun 17, 2016 7:02:55 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

You will now be able to access APEX in the database using the following URL.

http://ol7-121.localdomain:8080/ords/pdb1/

There is a configuration file for every database user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/pdb1*
cat /u01/ords/conf/ords/url-mapping.xml

Create a New Database Connection for PLSQL Applications

If you have web applications built using the PL/SQL Web Toolkit, you can deploy them using ORDS, in a similar way to using a DAD. In this case, each schema that requires PL/SQL to be exposed will need a separate database connection.

We create a new user and create a stored procedure to run.

CONN sys@pdb1 AS SYSDBA

DROP USER plsqluser CASCADE;
CREATE USER plsqluser IDENTIFIED BY plsqluser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE PROCEDURE TO plsqluser;

CONN plsqluser/plsqluser1@pdb1

CREATE OR REPLACE PROCEDURE plsql_test (p_text IN VARCHAR2) AS
BEGIN
  HTP.print(p_text);
END;
/

The following example creates a new database connection called "plsqluser-pdb1", which connects to the "plsqluser" user on the "pdb1" database. The database connection does not support Oracle REST Data Services or APEX.

$ $JAVA_HOME/bin/java -jar ords.war setup --database plsqluser-pdb1
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:2
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:plsqluser
Enter the database password for plsqluser:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2
Jun 17, 2016 7:09:16 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: plsqluser-pdb1
$

The main steps here are as follows.

With the new database connection in place, you need to map a URL to point to it.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /plsqluser-pdb1 plsqluser-pdb1
Jun 17, 2016 7:10:42 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/plsqluser-pdb1] to map to: [plsqluser-pdb1,,]
$

You will now be able to access the PL/SQL procedure using the following URL.

http://ol7-121.localdomain:8080/ords/plsqluser-pdb1/plsql_test?p_text=Banana

There is a configuration file for the user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/plsqluser-pdb1.xml
cat /u01/ords/conf/ords/url-mapping.xml

Create a New Database Connection for ORDS RESTful Services

From ORDS 3.0 onward, it is not mandatory to have an APEX installation. If you do not need to support APEX or PL/SQL applications, you could define a database connection as follows.

$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb2
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:pdb2
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:

Oracle REST Data Services will be installed in PDB2
Jun 17, 2016 7:43:21 PM oracle.dbtools.rt.config.setup.CdbPdbSetup isValidTablespace
INFO: Error the default tablespace APEX does not exist. An existing tablespace is required for ORDS installation.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Jun 17, 2016 7:43:27 PM oracle.dbtools.rt.config.setup.CdbPdbSetup isValidTablespace
INFO: Error the default tablespace APEX does not exist. An existing tablespace is required for ORDS installation.
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Jun 17, 2016 7:43:46 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: pdb2_pu
Installing Oracle REST Data Services version 3.0.5.124.10.54
... Log file written to /u01/ords/logs/ords_install_core_2016-06-17_194346_00302.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
Completed installation for Oracle REST Data Services version 3.0.5.124.10.54. Elapsed time: 00:00:37.218
$

The main steps here are as follows.

With the new database connection in place, you need to map a URL to point to it. Without a RESTful service in place, any request would result in a 404 error. We might map the following.

$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb2 pdb2
Jun 17, 2016 8:26:24 PM oracle.dbtools.url.mapping.file.MapURL execute
INFO: Creating new mapping from: [base-path,/pdb2] to map to: [pdb2,,]
$

If the "testuser1@pdb2" schema contained a REST service called "testmodule1/emp/", it might be accessed using the following type of URL. Creating REST services will be covered in a separate article.

http://ol7-121.localdomain:8080/ords/pdb2/testuser1/testmodule1/emp/

There is a configuration file for the user you defined, as well as an entry for the URL mapping.

ls /u01/ords/conf/ords/conf/pdb2*
cat /u01/ords/conf/ords/url-mapping.xml

JDBC Configuration

Check out the "defaults.xml" for parameters you may need to set to more realistic values, especially the "jdbc.MaxLimit" setting.

$ grep jdbc /u01/ords/conf/ords/defaults.xml
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InactivityTimeout">1800</entry>
<entry key="jdbc.InitialLimit">3</entry>
<entry key="jdbc.MaxConnectionReuseCount">1000</entry>
<entry key="jdbc.MaxLimit">10</entry>
<entry key="jdbc.MaxStatementsLimit">10</entry>
<entry key="jdbc.MinLimit">1</entry>
<entry key="jdbc.statementTimeout">900</entry>
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.