8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : Configure Multiple Databases (ORDS Versions 3.0 to 21.4)
This article is for ORDS versions 3.0 to 21.4. If you want to use version ORDS 22.1 onward, 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
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : Authentication
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.
- 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.
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.
- Request Path Prefix: A specific part or pattern in the URL, for example "/testdb/".
- Request URL Prefix: The base URL including the HTTP(s), for example "http://ol7-locadomain:8080/ords/testdb/".
The examples in this article will use the request path prefix approach.
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. In this article I will be using a 12c database, but it works just the same with an 11g database.
- Where necessary, the database already has a functioning APEX installation. This is not necessary for ORDS 3.x, but the APEX example will obviously need it.
Create a New Database Connection for APEX
The following examples create a new database connection called "pdb1", which supports APEX, APEX RESTful Services and Oracle REST Data Services. This can be done interactively or using a parameter file in silent node.
This shows how to do it interactively, without the parameter file.
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1 Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Enter the name of the database server [localhost.localdomain]: 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: Retrieving information. 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: Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3 2020-06-17T21:02:56.009Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|] 2020-06-17T21:02:56.031Z INFO Oracle REST Data Services schema version 19.4.6.r1421859 is installed. $
The main steps here are as follows.
- Enter hostname, port and service name for the database connection.
- Pick [1] for the Oracle REST Data Services to allow ORDS-style RESTful web services. It's not necessary for APEX, but it does no harm, so I always pick it. If you pick this option, you will have to enter your ORDS_PUBLIC_USER password.
- Pick [1] for the PL/SQL Gateway and accept the APEX_PUBLIC_USER username and provide the password.
- Pick [1] for the Application Express RESTful Services and provide the passwords for the APEX_LISTENER and APEX_REST_PUBLIC_USER users. This is not necessary if you are not planing to use the APEX RESTful web services, but I feel like you might as well configure everything.
- Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled.
We could achieve a similar result silently using a parameter file.
Create a file called "/u01/ords/params/pdb1_ords_params.properties" with the following contents.
db.hostname=localhost.localdomain db.port=1521 db.servicename=pdb1 #db.sid= db.username=APEX_PUBLIC_USER db.password=ApexPassword1 migrate.apex.rest=false plsql.gateway.add=true rest.services.apex.add=true rest.services.ords.add=true schema.tablespace.default=APEX schema.tablespace.temp=TEMP standalone.mode=false #standalone.use.https=true #standalone.http.port=8080 # ORDS19 Onward #standalone.static.path=/home/oracle/apex/images # Pre-ORDS19 #standalone.static.images=/home/oracle/apex/images user.apex.listener.password=ApexPassword1 user.apex.restpublic.password=ApexPassword1 user.public.password=ApexPassword1 user.tablespace.default=APEX user.tablespace.temp=TEMP sys.user=SYS sys.password=SysPassword1 restEnabledSql.active=false feature.sdw=false
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1 --parameterFile params/pdb1_ords_params.properties --silent Retrieving information. 2020-06-17T20:54:32.779Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|pu|, |pdb1|al|, |pdb1|rt|] 2020-06-17T20:54:32.873Z INFO Oracle REST Data Services schema version 19.4.6.r1421859 is installed. $
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:/localhost.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 examples create 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.
This shows how to do it interactively, without the parameter file.
$ $JAVA_HOME/bin/java -jar ords.war setup --database plsqluser-pdb1 Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Enter the name of the database server [localhost.localdomain]: 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 a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3 2020-06-17T21:16:24.054Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||] $
The main steps here are as follows.
- Enter hostname, port and service name for the database connection.
- Pick [2] so Oracle REST Data Services are not enabled.
- Pick [1] for the PL/SQL Gateway, but specify plsqluser for the username and provide the password.
- Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled.
We could achieve a similar result silently using a parameter file.
Create a file called "/u01/ords/params/plsqluser-pdb1_ords_params.properties" with the following contents.
db.hostname=localhost.localdomain db.port=1521 db.servicename=pdb1 #db.sid= db.username=plsqluser db.password=plsqluser1 migrate.apex.rest=false plsql.gateway.add=true rest.services.apex.add=false rest.services.ords.add=false #schema.tablespace.default=APEX #schema.tablespace.temp=TEMP standalone.mode=false #standalone.use.https=true #standalone.http.port=8080 # ORDS19 Onward #standalone.static.path=/home/oracle/apex/images # Pre-ORDS19 #standalone.static.images=/home/oracle/apex/images #user.apex.listener.password=ApexPassword1 #user.apex.restpublic.password=ApexPassword1 #user.public.password=ApexPassword1 #user.tablespace.default=APEX #user.tablespace.temp=TEMP sys.user=SYS sys.password=SysPassword1 restEnabledSql.active=false feature.sdw=false
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war setup --database plsqluser-pdb1 --parameterFile params/plsqluser-pdb1_ords_params.properties --silent Retrieving information. 2020-06-17T21:26:35.705Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||] $
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 2020-06-17T21:19:15.634Z INFO Creating new mapping from: [base-path,/plsqluser-pdb1] to map to: [plsqluser-pdb1, null, null] $
You will now be able to access the PL/SQL procedure using the following URL.
http://localhost.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.
This shows how to do it interactively, without the parameter file.
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb2 Specify the database connection type to use. Enter number for [1] Basic [2] TNS [3] Custom URL [1]: Enter the name of the database server [localhost.localdomain]: 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 [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: Retrieving information. 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 Enter a number to select a feature to enable [1] SQL Developer Web [2] REST Enabled SQL [3] None [1]:3 2020-06-17T21:42:16.159Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |plsqluser-pdb1||, |pdb2|pu|] 2020-06-17T21:42:16.179Z INFO Oracle REST Data Services schema version 19.4.6.r1421859 is installed. $
The main steps here are as follows.
- Enter hostname, port and service name for the database connection. Notice we connected to a different database this time.
- Pick [1] so Oracle REST Data Services are enabled. Since this is the first time ORDS has connected to this server it requires SYSDBA credentials so it can build the ORDS components.
- The tablespace errors are because ORDS is trying to reuse the settings in the "/u01/ords/params/ords_params.properties" file. If you want to avoid these, alter the contents of that file before you begin. The errors do not cause a problem as the setup allows you to specify the correct values.
- Pick [2] so the PL/SQL Gateway is not configured, as we don't want to configure APEX or direct PL/SQL applications.
- Pick [3] so SQL Developer Web and REST Enabled SQL are not enabled.
We could achieve a similar result silently using a parameter file.
Create a file called "/u01/ords/params/pdb2_ords_params.properties" with the following contents.
db.hostname=localhost.localdomain db.port=1521 db.servicename=pdb2 #db.sid= db.username=ORDS_PUBLIC_USER #db.password=ApexPassword1 migrate.apex.rest=false plsql.gateway.add=false rest.services.apex.add=false rest.services.ords.add=true #schema.tablespace.default=APEX #schema.tablespace.temp=TEMP standalone.mode=false #standalone.use.https=true #standalone.http.port=8080 # ORDS19 Onward #standalone.static.path=/home/oracle/apex/images # Pre-ORDS19 #standalone.static.images=/home/oracle/apex/images #user.apex.listener.password=ApexPassword1 #user.apex.restpublic.password=ApexPassword1 user.public.password=ApexPassword1 #user.tablespace.default=APEX #user.tablespace.temp=TEMP sys.user=SYS sys.password=SysPassword1 restEnabledSql.active=false feature.sdw=false
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war setup --database pdb2 --parameterFile params/pdb2_ords_params.properties --silent Retrieving information. 2020-06-17T21:59:23.683Z INFO reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|, |pdb1||, |pdb1|al|, |pdb1|pu|, |pdb1|rt|, |pdb2|pu|, |plsqluser-pdb1||] 2020-06-17T21:59:23.736Z INFO Oracle REST Data Services schema version 19.4.6.r1421859 is installed. $
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://localhost.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:
- Configuring Multiple Databases
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : Authentication
Hope this helps. Regards Tim...