This topic explains how to configure LabKey Server to retrieve and display data from an Oracle database as an external data source. This topic assumes you have reviewed the general guidance here and provides specific parameters and details for this database type.

• Oracle Version and JDBC Driver
• Configure the Oracle Data Source
• driverClassName
• url
• validationQuery
• Define a New External Schema
• Troubleshooting
• Recovery File Size
• Number (Decimal/Double) Data Truncates to Integer
• Open Cursor Limits

Oracle JDBC Driver

LabKey supports Oracle versions 10g through 23c and requires the Oracle JDBC driver to connect to Oracle databases. The driver is included with all Premium Edition distributions.

Configure the Oracle Data Source

The new external data source must have a unique JNDI name that you will use in naming the properties you will define. In the example on this page, we use "externalOracleDataSource", which will appear to users defining external schemas as "externalOracle". If you have more than one external data source, give each a unique name with the DataSource suffix ("firstExternalOracleDataSource", "secondExternalOracleDataSource", etc.). Learn more here.

In the <LABKEY_HOME>/config/application.properties file, add a new section with the name of the datasource and the parameters you want to define. Provide your own <SERVER:PORT>, <SERVICE>, <DB_USERNAME>, and <DB_PASSWORD> where indicated:

context.resources.jdbc.externalOracleDataSource.driverClassName=oracle.jdbc.driver.OracleDriver
context.resources.jdbc.externalOracleDataSource.url=jdbc:oracle:thin:@//<SERVER:PORT>/<SERVICE>
context.resources.jdbc.externalOracleDataSource.username=<DB_USERNAME>
context.resources.jdbc.externalOracleDataSource.password=<DB_PASSWORD>
context.resources.jdbc.externalOracleDataSource.validationQuery=SELECT 1 FROM dual

There are additional properties you can set, as shown in the template for the main "labkeyDataSource" in the application.properties file.

driverClassName

Use this as the driverClassName:

oracle.jdbc.driver.OracleDriver

url

The url property for Oracle takes this form, including the name of the database at the end:

jdbc:oracle:thin:@//<SERVER:PORT>/<SERVICE>

validationQuery

Use "SELECT 1 FROM dual" as the validation query for Oracle:

SELECT 1 FROM dual

Note that if this query does not succeed, you will be unable to connect to the datasource. One possible indication of an error executing this statement might be similar to:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Define a New External Schema

To define a new schema from this data source see Set Up an External Schema.

Troubleshooting

Validation Query

The validation query used for Oracle is "SELECT 1 FROM dual". If you cannot execute this query, your Oracle administrator may need to use a "public synonym" for dual.

Recovery File Size

• "ORA-19809: limit exceeded for recovery files"
• "ORA-03113: end-of-file on communication channel"
• "ORA-27101: shared memory realm does not exist"

1. Update the RMAN value for "db_recovery_file_dest_size" to be a bit higher (i.e. from 16G to 20G).
2. Restart your VM/machine.
3. Restart Oracle.

Number (Decimal/Double) Data Truncates to Integer

If you have a column of type NUMBER and its value is being shown as an integer instead of a decimal, the source of the problem may be a bug in the Oracle JDBC driver. More information can be found here:

• https://community.oracle.com/tech/developers/discussion/3677876/12775220-databasemetadata-getcolumns-returns-unexpected-values-in-column-size-and-decimal-digits
• https://stackoverflow.com/questions/34880444/is-there-any-significance-with-the-default-values-for-the-number-datatype-of-an

1. Set the following Java system property in the arguments used to launch the JVM:

   -Doracle.jdbc.J2EE13Compliant=true

Open Cursor Limits

LabKey has worked around this problem by estimating the number of cursors that are still available for the connection and discarding it when it approaches the limit. To accomplish this, the server issues the following query to Oracle:

SELECT VALUE FROM V$PARAMETER WHERE Name = 'open_cursors'

If the query cannot be executed, typically because the account doesn't have permission, LabKey will log a warning indicating it was unable to determine the max open cursors, and assume that Oracle is configured to use its default limit, 50. If Oracle's limit is set to 50 or higher, the warning can be safely ignored.

To avoid the warning message, grant the account permission to run the query against V$PARAMETER.

Related Topics

• Oracle FAQs: JDBC
• Oracle JDBC/UCP Download page
• External Schemas and Data Sources
• Supported Technologies