This topic explains how to configure LabKey server to retrieve and display data from an Oracle database as an external data source. LabKey supports Oracle versions 10g through 23c.

• Oracle JDBC Driver
• Configure the Oracle Data Source
• Define a New Schema
• Troubleshooting
• Connection Validation Query
• Recovery File Size
• Number (Decimal/Double) Data Truncates to Integer
• Open Cursor Limits

Oracle JDBC Driver

LabKey Server requires the Oracle JDBC driver to connect to Oracle databases. The driver is included with all Premium Edition distributions.

If you previously downloaded an ojdbc#.jar file, you must delete it from the CATALINA_HOME/lib directory to avoid a version conflict.

Configure the Oracle Data Source

Add a <Resource> element to your installation's labkey.xml configuration file. Use the template below as a general starting point, replacing the words in capitals with their appropriate values.

If you will be connecting to more than one data source, be sure that all have unique name parameters, i.e. oracleDataSource1, oracleDataSource2, or similar. This value will be used whenever you connect an external schema to this datasource so it must be unambiguous.

<Resource name="jdbc/oracleDataSource" 
       auth="Container"
       type="javax.sql.DataSource"
       driverClassName="oracle.jdbc.driver.OracleDriver"
       url="jdbc:oracle:thin:@//SERVER:PORT/SERVICE"
       username="USERNAME"
       password="PASSWORD"
       maxTotal="8"
       maxIdle="4" 
       accessToUnderlyingConnectionAllowed="true"
    />

Define a New Schema

Now define a new schema from the Oracle data source. For details see Set Up an External Schema.

Troubleshooting

As part of troubleshooting an Oracle data source connection, you can retry a failed connection without restarting your server.

Connection Validation Query

If desired, you can add a parameter to the resource tag to perform connection validation. For Oracle, the syntax is:

validationQuery="SELECT 1 FROM dual"

The error raised in case of a syntax error in this statement might be similar to:

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

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 Documents

• Oracle FAQs: JDBC
• Oracle JDBC/UCP Download page