java.lang.ArrayIndexOutOfBoundsException when creating a connection to an Oracle database

Question

It appears that Oracle's java client has a bug - if the tnsnames.ora file has misplaced spaces/tabs/new-lines in particular places, you get an exception with the following trace:

java.lang.ArrayIndexOutOfBoundsException: <some number>
        at oracle.net.nl.NVTokens.parseTokens(Unknown Source)
        at oracle.net.nl.NVFactory.createNVPair(Unknown Source)
        at oracle.net.nl.NLParamParser.addNLPListElement(Unknown Source)
        at oracle.net.nl.NLParamParser.initializeNlpa(Unknown Source)
        at oracle.net.nl.NLParamParser.<init>(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.loadFile(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.checkAndReload(Unknown Source)
        at oracle.net.resolver.TNSNamesNamingAdapter.resolve(Unknown Source)
        at oracle.net.resolver.NameResolver.resolveName(Unknown Source)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(Unknown Source)
        at oracle.net.ns.NSProtocol.establishConnection(Unknown Source)
        at oracle.net.ns.NSProtocol.connect(Unknown Source)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1037)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:282)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:468)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:839)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)

If you take a C++ application and try to connect with it to the database with the same tnsnames.ora in use - it works fine. Same goes for sqlplus. Also tnsping which should parse this file has no problem resolving any service name. Seems like Oracle were too lazy to .trim() the values or something - and it is the same problem with Oracle client versions 9, 10 and 11.

Any idea why this problem exists and what is the exact problem with the tnsnames.ora format? (I just remove all white-spaces to resolve it)

Answer 1

I tried the advice from GriffeyDog but unfortunately it did not solve the problem - so eventually I too the check for your self approach:

Oracle's documentation states that the structure of a record in the tnsnames.ora file should be as such:

net_service_name= 
 (DESCRIPTION=
   (ADDRESS=...)
   (ADDRESS=...)
   (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)))

Ours was:

net_service_name= 
(DESCRIPTION=
(ADDRESS=...)
(ADDRESS=...)
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)))

Apparently the indentation is crucial - if any of the lines in the block of a single net_service_name start at index 1 - this exception is thrown.

Only once you add indentation to all (can be spaces or tab) - it works. It doesn't have to look good, but has to have an offset of some sort.

Important note - the only problem is with '(', indentation rules don't apply to ')'.
E.g. the below example is perfectly fine:

net_service_name= 
     (DESCRIPTION=
       (ADDRESS=...
)
       (ADDRESS=...
)
       (CONNECT_DATA=
        (SERVICE_NAME=sales.us.example.com))
)

After searching for this issue to be documented - I finally found out that indeed it is documented at http://download.oracle.com/docs/cd/A57673_01/DOC/net/doc/NWUS233/apb.htm

And here is the important excerpt:

Even if you do not choose to indent your files in this way, you must indent a wrapped line by at least one space, or it will be misread as a new parameter. The following layout is acceptable:

(ADDRESS=(COMMUNITY=tcpcom.world)(PROTOCOL=tcp)
   (HOST=max.world)(PORT=1521))

The following layout is not acceptable:

(ADDRESS=(COMMUNITY=tcpcom.world)(PROTOCOL=tcp)
(HOST=max.world)(PORT=1521))