This sub will read the .ora file and pull out the information and place it in
columns A, B and C on a sheet you specify.
There is a definite possibility that you could get an "INPUT PAST END OF
FILE" error if the instance ID (DPS_DEV) is found, but then it can't find
either the HOST or SERVICE_NAME information. IF that happens, might as well
just hit the [End] button on the error alert window.

This could be modified so that you could type a number of the oracle
instance ids into a column on a worksheet, and it would go through the list
and pull out the related information from a specified .ora file. Or modified
to just select a .ora file and have it return the instance names, HOST and
SERVICE_NAME information for each one found in it.

Sub ReadOracleHostInfo()
'asks user for the Oracle instance ID, as DPS_DEV
'and returns the HOST=
'and SERVICE_NAME=
'information to a sheet you define below
Const rptSheetName = "Sheet2" ' change as desired
Const seek1 = "(HOST="
Const seek2 = "(SERVICE_NAME="

Dim oracleInstanceID As String
Dim hostFile As String
Dim hostBuffNum As Integer
Dim rptSheet As Worksheet
Dim fileText As String ' 1 line from .ora file
Dim ptr1 As Integer
Dim ptr2 As Integer

oracleInstanceID = _
InputBox("Enter the Oracle Instance ID to locate:", _
"Oracle Intance Name Entry", "")
If Trim(oracleInstanceID) = "" Then
Exit Sub
End If
'convert to all uppercase, and remove any
'leading/trailing whitespace characters
oracleInstanceID = UCase(Trim(oracleInstanceID))
'browse to select the Host.ora file to examine
hostFile = Application.GetOpenFilename
If Trim(UCase(hostFile)) = "FALSE" Then
Exit Sub ' user cancelled file selection
End If
Set rptSheet = ThisWorkbook.Worksheets(rptSheetName)
rptSheet.Cells.Clear
rptSheet.Range("A1") = "Instance"
rptSheet.Range("B1") = "HOST="
rptSheet.Range("C1") = "SERVICE NAME"

hostBuffNum = FreeFile()
Open hostFile For Input As #hostBuffNum
Do While Not EOF(hostBuffNum)
Line Input #hostBuffNum, fileText
'searching for the oracleInstanceID
If InStr(fileText, oracleInstanceID) 0 Then
'echo to worksheet to acknowledge that we found it
rptSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
oracleInstanceID
'now we look for the HOST= information
Do Until InStr(fileText, seek1) 0
Line Input #hostBuffNum, fileText
Loop
'presumably we now have the line with the HOST=
'information in it
ptr1 = InStr(fileText, seek1)
ptr2 = InStr(ptr1, fileText, ")")
rptSheet.Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = _
Mid(fileText, ptr1, ptr2 - ptr1 + 1)
'continue on to look for the SERVICE_NAME information
Do Until InStr(fileText, seek2) 0
Line Input #hostBuffNum, fileText
Loop
'presumably we now have the line with the SERVICE_NAME=
'information in it
ptr1 = InStr(fileText, seek2)
ptr2 = InStr(ptr1, fileText, ")")
rptSheet.Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = _
Mid(fileText, ptr1, ptr2 - ptr1 + 1)
Exit Do ' all done, quit now
End If
Loop
Close #hostBuffNum
End Sub


"Spike" wrote:

I will be very grateful for the code to extract from a .ora file €œHost€� name
and €œService Name€� for say DPS_DEV. I give below an extract from the file
which has about 20 sections. Thisis a file that details the addresses etc
of the various databases. I need to pick up the above for a connection
string to an Oracle database.

Any advice will be very gratefully received

UAT_TTPLUS1=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=TT-UAT)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=tt01)
)
)


DPS_DEV=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=VDBDEVORA)(PORT=1521) )
)
(CONNECT_DATA=
(SERVICE_NAME=DPSD)
)
)

DPS_DR=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=DPS02)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=DPSDR)
)
)


--
with kind regards

Spike