| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parsing tnsnames.ora
"Orr, Steve" wrote:
>
> Anyone have a ready-made regular expression to parse out the net8 service
> name labels from tnsnames.ora? Perl is OK. What I'm looking for is a way to
> get a list of possible connections from tnsnames.ora. For example, from the
> below I just want a regular expression which returns label1 and label2...
> --------------------------------------------------------
> label1 = (description_list=
> (description=
> (address=(...))
> (connect_data=(...)))
> (description=
> (address=(...))
> (connect_data=(...)))
> )
>
> label2 = (description_list=
> (description=
> (address=(...))
> (connect_data=(...)))
> (description=
> (address=(...))
> (connect_data=(...)))
> )
> --------------------------------------------------------
>
> AtDhVaAnNkCsE !!!!!!!!
>
> Steve Orr
> Bozeman, Montana
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
Steve,
You may be interested by one of the numerous things our webmaster forgot to post on the Oriole site :
==============CUT HERE=====================
/*
* * tnsparse.c - Copyright (c) Oriole Software, 2001 * * Downloaded from http://www.oriole.com * * Reads a tnsnames.ora file on its standard input and writes * tnsalias<tab>host<tab>sid * to its standard output. * * This can be used for a number of things: * o to generate a clean inventory and load it into, say, a spreadsheet * or build a HTML page. This can easily be done with the following * awk program : * * BEGIN { * print "<HTML>"; * print "<HEAD>"; * print " <TITLE>Oracle databases</TITLE>"; * print "</HEAD>"; * print "<BODY TEXT=BLACK BGCOLOR=WHITE>"; * print "<CENTER>"; * print "<TABLE WIDTH=\"80%\">"; * print "<TR BGCOLOR=NAVY>"; * print "<TD><B><FONT COLOR=GOLD>TNS Alias</FONT></B></TD>"; * print "<TD><B><FONT COLOR=GOLD>Host</FONT></B></TD>"; * print "<TD><B><FONT COLOR=GOLD>Service Name</FONT></B></TD>"; * print "</TR>"; * } * { * if (NR %2 == 1) * { * print "<TR BGCOLOR=LIGHTCYAN>"; * } * else * { * print "<TR BGCOLOR=LIGHTSKYBLUE>"; * } * for (i = 1; i <= NF; i++) * { * printf("<TD>%s</TD>\n", $i); * } * print "</TR>"; * } * END { * print "</TABLE>"; * print "</CENTER>"; * print "</BODY>"; * print "</HTML>"; * } * * o to clean-up (ordering by alphabetical order, say) or merge easily * (removing duplicate entries) existing tnsnames.ora file. * Assuming that you only have TCP/IP and are always using the same * port (say 1526), a clean tnsnames.ora file can easily be * regenerated feeding the (cooked) output of tnsparse into an awk * programme such as : * * { * printf("%s=\n", $1); * printf("(DESCRIPTION =\n"); * printf(" (ADDRESS_LIST =\n"); * printf(" (ADDRESS =\n"); * printf(" (COMMUNITY = your_company.world)\n"); * printf(" (PROTOCOL = TCP)\n"); * printf(" (Host = %s)\n", $2); * printf(" (port = 1526)\n"); * printf(" )\n"); * printf(" ) \n"); * printf(" (CONNECT_DATA =\n"); * printf(" (SID = %s)\n", $3); * printf(" (GLOBAL_NAME = %s.WORLD)\n", $3); * printf(" )\n"); * printf(") \n"); * printf("\n"); * }
* * To build tnsparse : * cc tnsparse.c -o tnsparse * * To use it : * tnsparse < $ORACLE_HOME/network/admin/tnsnames.ora >tnslist.txt
* * Tries to support both pre and post 8.1 formats. * * Note that the program is a bit too sophisticated for what it does. * We hope that it will make it easier for you to modify it and extract * more data (for instance, the listener port when using TCP/IP) to suit * your needs.
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <ctype.h>
#define STACK_SIZE 100
#define LINE_LEN 1024 #define TNS_LEN 100 #define HOST_LEN 512 #define SID_LEN 100 #define MAX_LEN 512 #define KEYWORDS 8 #define KW_ADDRESS 0 #define KW_ADDRESS_LIST 1 #define KW_CONNECT_DATA 2 #define KW_DESCRIPTION 3 #define KW_DESCRIPTION_LIST 4 #define KW_HOST 5 #define KW_SERVICE_NAME 6 #define KW_SID 7 #define KW_IGNORED KEYWORDS static char *G_stack[STACK_SIZE]; static short G_st = 0; static char G_tns_alias[TNS_LEN];
static char *G_kw[KEYWORDS+1] = {"ADDRESS",
"ADDRESS_LIST",
"CONNECT_DATA",
"DESCRIPTION",
"DESCRIPTION_LIST",
"HOST",
"SERVICE_NAME",
"SID",
NULL};
static short which_kw(char *string)
{
short i = KW_IGNORED;
if (string)
{
G_kw[i] = string;
i = 0;
while (strcasecmp(string, G_kw[i]))
{
i++;
}
static char check(short kw)
/* * A bit of an overkill, but I am a paranoid. */
if (0 == G_st)
{
return(0);
}
prev = which_kw(G_stack[G_st - 1]);
switch (kw)
{
case KW_HOST :
return(KW_ADDRESS == prev);
case KW_SERVICE_NAME :
case KW_SID :
return(KW_CONNECT_DATA == prev);
default :
return(0);
static void store(char *identifier)
{
if (identifier)
{
if (G_stack[G_st] != NULL)
{
free(G_stack[G_st]);
}
G_stack[G_st] = strdup(identifier);
}
static char *peek(void)
{
if (G_st)
{
return(G_stack[G_st]);
}
return(NULL);
}
static void output(void)
{
printf("%s\t%s\t%s\n", G_tns_alias, G_host, G_service);
G_host[0] = '\0';
G_service[0] = '\0';
}
main()
{
char line[LINE_LEN];
char identifier[MAX_LEN];
char *p;
short kw;
short i;
char pending = 0;
/* * Initialize */
{
G_stack[i] = NULL;
}
G_tns_alias[0] = '\0'; G_host[0] = '\0'; G_service[0] = '\0';
{
if (line[0] != '#')
{
p = line;
i = 0;
while (*p != '\n')
{
switch(*p)
{
case '=' :
identifier[i] = '\0';
store(identifier);
if (0 == G_st)
{
if (pending)
{
output();
pending = 0;
}
strncpy(G_tns_alias, identifier, TNS_LEN);
G_tns_alias[TNS_LEN-1] = '\0';
pending = 1;
}
i = 0;
break;
case ' ' :
case '\t' :
break;
case '(' :
G_st++;
break;
case ')' :
identifier[i] = '\0';
switch (kw = which_kw(peek()))
{
case KW_HOST :
if (check(kw))
{
strncpy(G_host, identifier,
HOST_LEN);
G_host[HOST_LEN-1] = '\0';
if (G_service[0] != '\0')
{
output();
pending = 0;
}
else
{
pending = 1;
}
}
break;
case KW_SERVICE_NAME :
case KW_SID :
if (check(kw))
{
strncpy(G_service,
identifier,
SID_LEN);
G_service[SID_LEN-1] = '\0';
if (G_host[0] != '\0')
{
output();
pending = 0;
}
else
{
pending = 1;
}
break;
default :
break;
}
}
G_st--;
i = 0;
break;
default :
identifier[i] = *p;
i++;
break;
}
p++;
}
}
{
output();
}
/* * Do things cleanly, this is not Java ... */
{
free(G_stack[i++]);
}
exit(0);
-- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Nov 12 2002 - 13:57:54 CST
 |
 |