Have you ever wanted to use a tool to parse the manually typed up “stuff” that lives in a tnsnames.ora file, to be absolutely certain that it is correct? Ever wanted some tool to count all the opening and closing brackets match? I may just have the very thing for you.

All the grammar, lexer and parser code can be found on my GitHub. The readme file is your friend.

Download the binary file TNSNames.Parser.zip and unzip it. Source code is also available on Github.

When unzipped, you will see the following files:

  • README.* - this should be obvious!
  • tnsnames_checker.sh - Unix script to run the utility.
  • tnsnames_checker.cmd - Windows batch file to run the utility.
  • antlr-4.8-complete.jar - Parser support file.
  • tnsnames_checker.jar - Parser file.
  • tnsnames.test.ora - a seriously invalid tnsnames.ora to test the utility with.

The README file is your best friend!

All the utility does is scan the supplied input file, passed via standard in, and writes any syntax or semantic problems out to standard error.

Before You Begin

The files in the download ar the raw files, I wrote the grammar, lexer and parser, a couple of test harnesses and that’s what you get. See this post for a full-blown utility to do all the hard work for you.

Working Example

If there were no errors in the tnsnames.test.ora file, the output would look like the following:

./tnsnames_checker.sh < tnsnames.test.ora

Tnsnames Checker.
Using grammar defined in tnsnames.g4.
Parsing ....
Done.

Non-Working Example

As there are numerous, and I mean numerous, errors in the test file, the output actually looks like the following:

./tnsnames_checker.sh < tnsnames.test.ora

================================================================================
Tnsnames Checker - Version 0.6.
================================================================================

Using TNSNAMES specification version for Oracle 12c (12.2), as defined at:
	http://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm. (11GR2)

	https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netrf/local-naming-parameters-in-tnsnames-ora-file.html. (12c)
Using grammar defined in tnsnamesParser.g4, downloadable from:
	https://github.com/NormanDunbar/grammars-v4/tree/master/tnsnames.

The official ANTLR version is downloadable from:
	https://github.com/antlr/grammars-v4/tree/master/tnsnames.
(This may not be as up to date if there are any pull requests outstanding.)

================================================================================
Parsing tnsnames file 'Standard Input' ...
================================================================================
line 210:7 mismatched input ')' expecting '('

Line 12:1  Listener alias found: LSNR_FRED
*** INFO: 0, WARNING: 0, ERRORS: 0

Line 21:1  Listener alias found: LSNR_HEXFRED
*** INFO: 0, WARNING: 0, ERRORS: 0

Line 30:1  Listener alias found: LSNR_OCTFRED
*** INFO: 0, WARNING: 0, ERRORS: 0

Line 39:1  Listener alias found: LSNR_MIXFRED
*** INFO: 0, WARNING: 0, ERRORS: 0

Line 48:1  Listener alias found: LSNR_MESSFRED
	Line 50:40 ERROR: dotQuad '0xFe0' in '10.0xFe0.029.0X27' out of range 0 - 254.
*** INFO: 0, WARNING: 0, ERRORS: 1

Line 57:1  Listener alias found: LSNR_MESSFRED
	Line 57:1 ERROR: Duplicate alias - LSNR_MESSFRED
	Line 59:40 ERROR: dotQuad '0xFe0' in '10.0xFe0.029.0X27' out of range 0 - 254.
*** INFO: 0, WARNING: 0, ERRORS: 2

Line 66:1  Listener alias found: LSNR_WILMA
	Line 67:46 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 67.
	Line 67:62 WARNING: KEY parameter redefines KEY parameter at line 67.
*** INFO: 0, WARNING: 2, ERRORS: 0

Line 74:1  Listener alias found: lsnr_barney
*** INFO: 0, WARNING: 0, ERRORS: 0

Line 86:1  Listener alias found: lsnr_betty
	Line 87:3 WARNING: Missing ADDRESS_LIST, 2 ADDRESS entries found.
*** INFO: 0, WARNING: 1, ERRORS: 0

Line 107:1  Database alias found: alias_1,alias_2.world,alias3.dunbar-it.co.uk
	Line 110:8 WARNING: LOAD_BALANCE parameter redefines LOAD_BALANCE parameter at line 109.
	Line 119:84 WARNING: Port number, 80 < 1024. May be invalid.
	Line 120:52 ERROR: dotQuad '0' in '0.2.3.400' out of range 1 - 254.
	Line 120:52 ERROR: dotQuad '400' in '0.2.3.400' out of range 0 - 254.
	Line 125:21 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 123.
	Line 127:21 WARNING: PORT parameter redefines PORT parameter at line 126.
	Line 128:21 WARNING: HOST parameter redefines HOST parameter at line 124.
	Line 130:85 ERROR: Port number 65536. Out of range 1024 - 65535.
	Line 135:21 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 133.
	Line 136:21 WARNING: KEY parameter redefines KEY parameter at line 134.
	Line 143:28 WARNING: SEND_BUF_SIZE parameter redefines SEND_BUF_SIZE parameter at line 141.
	Line 144:28 WARNING: RECV_BUF_SIZE parameter redefines RECV_BUF_SIZE parameter at line 142.
	Line 149:21 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 146.
	Line 156:21 WARNING: ARGV0 parameter redefines ARGV0 parameter at line 154.
	Line 157:21 WARNING: PROGRAM parameter redefines PROGRAM parameter at line 153.
	Line 158:21 WARNING: ARGS parameter redefines ARGS parameter at line 155.
	Line 163:21 WARNING: ARGV0 parameter redefines ARGV0 parameter at line 162.
	Line 164:84 WARNING: LOCAL parameter redefines LOCAL parameter at line 164.
	Line 164:96 WARNING: ADDRESS parameter redefines ADDRESS parameter at line 164.
	Line 164:107 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 164.
	Line 169:28 WARNING: PIPE parameter redefines PIPE parameter at line 168.
	Line 170:28 WARNING: SERVER parameter redefines SERVER parameter at line 167.
	Line 171:28 WARNING: PROTOCOL parameter redefines PROTOCOL parameter at line 166.
	Line 175:13 ERROR: SDU value 256. Out of range 512 - 65535.
	Line 176:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 177:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 178:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 178:13 INFO: SDU value 8192. This is the default setting.
	Line 179:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 180:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 180:13 ERROR: SDU value 65536. Out of range 512 - 65535.
	Line 183:28 WARNING: SID parameter redefines SID parameter at line 183.
	Line 183:41 WARNING: SID parameter redefines SID parameter at line 183.
	Line 186:15 WARNING: SERVER parameter redefines SERVER parameter at line 184.
	Line 191:15 WARNING: HS parameter redefines HS parameter at line 188.
	Line 192:15 WARNING: UR parameter redefines UR parameter at line 190.
	Line 193:15 WARNING: SERVICE_NAME parameter redefines SERVICE_NAME parameter at line 185.
	Line 194:15 WARNING: SERVICE_NAME parameter redefines SERVICE_NAME parameter at line 185.
	Line 196:13 WARNING: RETRY_COUNT parameter redefines RETRY_COUNT parameter at line 181.
	Line 199:13 WARNING: SDU parameter redefines SDU parameter at line 175.
	Line 199:13 ERROR: SDU value 65536. Out of range 512 - 65535.
	Line 200:13 WARNING: SEND_BUF_SIZE parameter redefines SEND_BUF_SIZE parameter at line 112.
	Line 201:13 WARNING: RECV_BUF_SIZE parameter redefines RECV_BUF_SIZE parameter at line 115.
	Line 203:8 WARNING: Missing ADDRESS_LIST, 4 ADDRESS entries found.
	Line 205:48 ERROR: dotQuad '255' in '10.0.254.255' out of range 0 - 254.
*** INFO: 1, WARNINGS: 37, ERRORS: 7

Line 217:1  Database alias found: barney
*** INFO: 0, WARNINGS: 0, ERRORS: 0

Line 230:1  Database alias found: alias3.dunbar-it.co.uk
	Line 230:1 ERROR: Duplicate alias - alias3.dunbar-it.co.uk
*** INFO: 0, WARNINGS: 0, ERRORS: 1

Line 243:1  Database alias found: pebbles
*** INFO: 0, WARNINGS: 0, ERRORS: 0

Line 263:1  Database alias found: pebbles_two
	Line 276:11 WARNING: FAILOVER_MODE parameter redefines FAILOVER_MODE parameter at line 267.
	Line 281:17 WARNING: TYPE parameter redefines TYPE parameter at line 280.
	Line 282:17 WARNING: METHOD parameter redefines METHOD parameter at line 277.
	Line 284:17 WARNING: BACKUP parameter redefines BACKUP parameter at line 278.
	Line 285:17 WARNING: DELAY parameter redefines DELAY parameter at line 279.
	Line 286:17 WARNING: RETRIES parameter redefines RETRIES parameter at line 283.
	Line 287:17 WARNING: TYPE parameter redefines TYPE parameter at line 280.
*** INFO: 0, WARNINGS: 7, ERRORS: 0

Line 299:1  Database alias found: alias.foo.bar
*** INFO: 0, WARNINGS: 0, ERRORS: 0

Line 311:1  Database alias found: valid_12c
*** INFO: 0, WARNINGS: 0, ERRORS: 0

Line 333:1  Database alias found: valid_tcps
*** INFO: 0, WARNINGS: 0, ERRORS: 0

Line 364:1  Database alias found: valid_exadirect
*** INFO: 0, WARNINGS: 0, ERRORS: 0

================================================================================
Parsing Information:
===================
INFORMATION      : 1
PARSER WARNINGS  : 47
PARSER ERRORS    : 11
DUPLICATE ENTRIES: 2
	Duplicate[0] = 'LSNR_MESSFRED'
	Duplicate[1] = 'alias3.dunbar-it.co.uk'
================================================================================

--------------------------------------------------------------------------------
IFILE List:
--------------------------------------------------------------------------------
Please run the tnsnames_checker script on the following 3 files:
	IFILE[0] = '/this/is/a/double_quoted/ifile/entry/tnsnames.ora' (not found on this computer.)
	IFILE[1] = '/this/is/a/single_quoted/ifile/entry/tnsnames.ora' (not found on this computer.)
	IFILE[2] = '/this/is/an/unquoted/ifile/entry/tnsnames.ora' (not found on this computer.)
End of IFILE List.
================================================================================
Parsing tnsnames file 'Standard Input' complete.
================================================================================

You can figure out where and what went wrong from the messages produced.

Have fun.