Fetching connection data from tnsnames.ora via awk

Question

I try to build automation procedure which base on connection data from tnsnames.ora which is oracle connection base for database.

Typical TNS build looks like this but there is much more connections than 1:

" 

MY_BASE= 
  (DESCRIPTION= 
    (ADDRESS= 
      (PROTOCOL=TCP) 
      (HOST=10.20.30.40) 
      (PORT=1234) 
    ) 
    (CONNECT_DATA= 
      (SERVER=dedicated) 
      (SERVICE_NAME=MY_SERVICE) 
    ) 
  ) 

"

I need to extract base connection data which would be: BASE_NAME HOST PORT SERVICE_NAME more or less would look like

MY_BASE 10.20.30.40 1234 MY_SERVICE

Some of connections got more than one service but I will not bother you with these cause I have no idea how to make it as variable at the end :P

My point is to make a list in jenkins automation and make it to return list of connections in a checkbox. Than I and make variables out of those connection data so I will be able to do the same thing on many databases at the same time.

More or less...

Any idea? What to read, what to find, what to learn from AWK(cause it is a BIG tool).

Best regards!

Why don't you use the **tnsnames.ora** file to establish the connection? See e.g. [here](https://stackoverflow.com/questions/14194750/java-jdbc-how-to-connect-to-oracle-using-tnsnames-ora) — Marmite Bomber, May 28 '18 at 16:49

score 1 · Answer 1 · answered May 28 '18 at 14:33

If this isn't all you need:

$ cat tst.awk
BEGIN { FS="[()=]" }
NF==2 && /=/ { f["BASE_NAME"] = $1 }
NF==4 { f[$2] = $3 }
END { print f["BASE_NAME"], f["HOST"], f["PORT"], f["SERVICE_NAME"] }

$ awk -f tst.awk file
MY_BASE 10.20.30.40 1234 MY_SERVICE

then edit your question to clarify your requirements and provide more truly representative sample input/output.

RavinderSingh13 · Answer 2 · 2018-05-28T14:50:32.483

Following awk may help you here(in case of multiple entries of TNS too it should work).

awk -F"[=)]" '
/^"/{
  flag=1;
  next}
flag && NF{
  value=$1;
  flag=""}
/HOST/{
  value=value OFS $(NF-1);
  next}
/PORT/{
  value=value OFS $(NF-1);
  next}
/SERVICE_NAME/{
  print value OFS $(NF-1);
  value=""}
END{
  if(value){
    print value}
}'   Input_file

Execution of above command: Let's say following is the test Input_file.

cat Input_file
"

MY_BASE=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=yourip)
      (PORT=1234)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=MY_SERVICE)
    )
  )
"

"

MY_BASE=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=yourip)
      (PORT=1234)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=MY_SERVICE)
    )
  )


"

Now after running above code following will the output.

MY_BASE yourip 1234 MY_SERVICE
MY_BASE yourip 1234 MY_SERVICE

score 0 · Answer 3 · answered May 29 '18 at 09:25

First of all, thank you for your answers. Generally I have checked and it looks like that my tnsnames isnt that standarized as I yough.

First results are good, like

MY_BASE 10.20.30.40 1234 MY_SERVICE

But further analysis shows that results looks like...

10.20.30.40 1234 MY_SERVICE MY_BASE

or

MY_SERVICE 1234 10.20.30.40

etc...

Whole results moves and I cant make a variable of it.

Is there away to prevent result from dropping into second line or to prefent few different results in one line?

Like make it to typically read that... Set in new line result started with LETTER (not number or special sign) and ended with a " = " sighn. Set after it value betwean HOST= and nearest ")" sign and leave spacia after it. Set after it value betwean PORT= to nearest ")" signt and leave space after it. Set after it value betwean SERVICE_NAME= to nearest ")" and leave space after it.

And again new word started with a letter which have "=" at the ends starts it again.

Well... it is my way of thinking. I have no idea is it a proper one :/ Thank you once again.

Best regards.

Fetching connection data from tnsnames.ora via awk

3 Answers3