8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Background and Assumptions
- Create a New Database Connection for APEX
- Create a New Database Connection for PLSQL Applications
- Create a New Database Connection for ORDS RESTful Services
- JDBC Configuration
Related articles.
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat (ORDS Version 22.1 Onward)
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.
- Deploy APEX applications from multiple databases.
- Deploy PL/SQL Web Toolkit applications from multiple databases or multiple schemas within a single database.
- Deploy REST web services from multiple databases.
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.
- You already have a functioning installation of ORDS.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have one or more Oracle databases available.
- Where necessary, the database already has a functioning APEX installation. This is not necessary for ORDS, but the APEX example will obviously need it.
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.
- We supply the location of the configuration and log folders. If the
ORDS_CONFIG
environment variable is set, we don't really need to use the--config
argument, but I prefer to be explicit. - We supply the pool name using the
--db-pool
argument. If this is omitted, the default configuration will be altered, rather than a new pool being created. - We supply the database connection details.
- We've enabled the DB API, Rest Enabled SQL and SQL Developer Web. These aren't necessary, but they are nice to include.
- We set the
--gateway-mode
argument to proxied, and the--gateway-user
is set toAPEX_PUBLIC_USER
. - We supply the passwords for the
SYSDBA
andORDS_PUBLIC_USER
users using file redirection.
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.
- We supply the location of the configuration and log folders.
- We supply the pool name using the
--db-pool
argument. - We supply the database connection details.
- We set the
--gateway-mode
argument to proxied, and the--gateway-user
is set toPLSQLUSER
. - We supply the passwords for the
SYSDBA
andORDS_PUBLIC_USER
users using file redirection.
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.
- We supply the location of the configuration and log folders.
- We supply the pool name using the
--db-pool
argument. - We supply the database connection details.
- We set the
--gateway-mode
argument to disabled. - We supply the passwords for the
SYSDBA
andORDS_PUBLIC_USER
users using file redirection.
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:
- Configuring Oracle REST Data Services for Multiple Databases
- About the Oracle REST Data Services Configuration Files
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat (ORDS Version 22.1 Onward)
Hope this helps. Regards Tim...