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