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

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Configure Multiple Databases (ORDS Version 22.1 Onward)

This article is for ORDS version 22.1 onward. If you want to use a version prior to ORDS 22.1, use this article.

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

This article assumes the following.

The examples assume the following environment variables exist to set the relevant paths.

export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}
export ORDS_LOGS=${ORDS_CONFIG}/logs

Create a New Database Connection for APEX

The following examples create a new database connection called "pdb1", which supports APEX and Oracle REST Data Services. For each new connection we have to install ORDS in the destination database. This can be done interactively or using command line arguments. Here we use command line arguments to create the new pool.

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --feature-db-api true \
     --feature-rest-enabled-sql true \
     --feature-sdw true \
     --gateway-mode proxied \
     --gateway-user APEX_PUBLIC_USER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

The main steps here are as follows.

Once the command has run, we can see a new subdirectory for "pdb1" under the databases configuration.

$ cd $ORDS_CONFIG/databases/
$ tree
.
├── default
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
└── pdb1
    ├── pool.xml
    └── wallet
        └── cwallet.sso

4 directories, 4 files
$

We now have two paths available, representing the two connections. The URL will be different, depending on if you configured ORDS to use HTTPS.

HTTPS:
Default: http://localhost:8080/ords/
PDB1   : http://localhost:8080/ords/pdb1/

HTTPS:
Default: https://localhost:8443/ords/
PDB1   : https://localhost:8443/ords/pdb1/

In this example we named the new pool after the database connection, but we could have given it a different name that looks better in the URL.

Create a New Database Connection for PLSQL Applications

If we have web applications built using the PL/SQL Web Toolkit, we 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/SysPassword1@//localhost:1521/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@//localhost:1521/pdb1

create or replace procedure plsql_test (p_text in varchar2) as
begin
  htp.print(p_text);
end;
/

We have two options for creating the pool. We can use a proxied connection, via the ORDS_PUBLIC_USER, or connect directly to the schema with the PL/SQL we need to run.

The following example creates a new database pool called "plsqluser-pdb1", which connects to the PLSQLUSER user on the PDB1 database. The database connection uses proxy access, but does not support APEX.

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool plsqluser-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode proxied \
     --gateway-user PLSQLUSER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

The main steps here are as follows.

This example does a similar thing, but uses direct access instead of proxying. Notice the --gateway-mode is set to "direct" and we supply the password to the PLSQLUSER1 user.

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1
export DIRECT_PASSWORD=plsqluser1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool plsqluser-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode direct \
     --gateway-user PLSQLUSER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
${DIRECT_PASSWORD}
EOF

Regardless of the method used, once the command has run, we can see a new subdirectory for "plsqluser-pdb1" under the databases configuration.

$ cd $ORDS_CONFIG/databases/
$ tree
.
├── default
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
├── pdb1
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
└── plsqluser-pdb1
    ├── pool.xml
    └── wallet
        └── cwallet.sso

6 directories, 6 files
$

By default the "security.requestValidationFunction" attribute is set up for APEX, so we need to either provide a different security function, blank the value, or delete the setting to access our PL/SQL.

# Set to blank value.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set security.requestValidationFunction ""

# Remove the setting.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 delete security.requestValidationFunction

After restarting ORDS, we can now access the PL/SQL procedure using the following URL.

https://localhost:8443/ords/plsqluser-pdb1/plsql_test?p_text=Banana

Create a New Database Connection for ORDS RESTful Services

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.

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool rest-only-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode disabled \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

The main steps here are as follows.

Once the command has run, we can see a new subdirectory for "plsqluser-pdb1" under the databases configuration.

$ cd $ORDS_CONFIG/databases/
$ tree
.
├── default
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
├── pdb1
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
├── plsqluser-pdb1
│   ├── pool.xml
│   └── wallet
│       └── cwallet.sso
└── rest-only-pdb1
    ├── pool.xml
    └── wallet
        └── cwallet.sso

8 directories, 8 files
$

The base URL for this pool would now be as follows, but this is not a full URL to a web service.

https://localhost8443/ords/rest-only-pdb1/

A full URL would look more like this, with the appropriate values substituted for the web service.

https://localhost8443/ords/rest-only-pdb1/{schema-alias)/{module-name}/{template}/

JDBC Configuration

The default values for the JDBC connections are fine for testing, but you will probably need to increase them for production systems that are under load. They can be set at the default level, or on a per-pool basis.

# Set limits for default pool.
ords --config ${ORDS_CONFIG} config set jdbc.InitialLimit 5
ords --config ${ORDS_CONFIG} config set jdbc.MaxLimit 15

# Set limits for a specific pool.
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set jdbc.InitialLimit 5
ords --config ${ORDS_CONFIG} config --db-pool plsqluser-pdb1 set jdbc.MaxLimit 15

We must restart ORDS for these settings to take effect. The full list of configurable settings can be found here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.