PowerShellWindows PowerShell

Retrieve Oracle instance port number using PowerShell | REGEX

A requirement popped up to get the Oracle instance(s) port number before building a connection string for establishing a connection. Team used tnsping utility which returns results like shown below

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 09-MAR-2018 04:44:04

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
D:\app\oracle\product\11.2.0\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORAHOSTNAME)(PORT = 15210))) (CONNECT_DATA = (SERVICE_NAME = oracle)))
OK (20 msec)

We need to get the port number – Which is really a very straight forward approach using regular expression

REGEX

$inputData = @"
TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 09-MAR-2018 04:44:04

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
D:\app\oracle\product\11.2.0\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER123)(PORT = 15210))) (CONNECT_DATA = (SERVICE_NAME = oracle)))
OK (20 msec)
"@;

$data = [Regex]::Match($inputData, "(PORT = )\d+").Value;

$data

Result : PORT = 15210

 

Leave a Reply

Your email address will not be published. Required fields are marked *