Sunday, June 14, 2015

Configuring Oracle ORDS/Apex Listener with Oracle OID/LDAP/TNS/EZ Connect

I have been using ORDS since Apex Listener 1.0 beta, however didn't figure out how to connect it to an Oracle OID. I feel ORDS documentation should include sample configurations for each connection types. Till then here is your unofficial documentation.

Following is tested with Apex Listener 2.0.10.289.08.09. However I believe will work with ORDS 3.0 as well.


#1: Connecting to Oracle OID

<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">
jdbc:oracle:thin:@ldap://ldap.example.com:7777/ORCL,cn=OracleContext,dc=example,dc=com
</entry>
<entry key="db.password">@encrypted password</entry>

References:
ORDS Configuration: https://docs.oracle.com/cd/E56351_01/doc.30/e56293/config_file.htm#AELIG7204
LDAP Syntax http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#CHDBICFA
Use ldaps for SSL.
ldap.example.com:7777 => Is your ldap server name and port
ORCL  => Database Service name configured in OID. Replace it with the Database you want to connect
cn=OracleContext  => Keep it. I believe this is something defined at the OID level
dc=example,dc=com  => Thats your domain. If you have 3 parts in your domaninlike example.co.uk then use dc=example,dc=co,dc=uk 

#2: Connecting using TNS Names
You can do it in two ways. You can have a tnsnames.ora file with database connect strings defined in it. For that use the following (Note: connectionType=tns)
<entry key="db.connectionType">tns</entry>
<entry key="db.tnsDirectory">/home/oracle/admin/tnsnames.ora</entry>

<entry key="db.tnsAliasName">ORCL</entry>
<entry key="db.password">@encrypted password</entry>

Or you can specify the entire TNS Connect string using db.CustomURL like below (Note: connectionType=customurl)
<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=myhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
</entry>
<entry key="db.password">@Your_encrypted_password</entry>

#3: Default EZ Connect method
This is the default when you configure ords war using $java -jar ords.war
You don't need to specify db.connectionType here, which default to "basic"
<entry key="db.hostname">myhost</entry>
<entry key="db.password">@ Your_encrypted_password </entry>
<entry key="db.port">1521</entry>

<entry key="db.servicename">ORCL</entry>