Prev: Form check box
Next: How reliable is this site!
From: JLatham on 29 Mar 2010 18:32
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
 | 
Pages: 1
Prev: Form check box
Next: How reliable is this site!