Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 29th 10, 11:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,203
Default extract data from .ora file

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract data from csv file L.Mathe Excel Programming 8 March 22nd 10 11:14 AM
Extract matching data from large data file (csv) Utahstew Excel Programming 8 April 14th 08 03:57 AM
Extract Certain Data from closed file joecrabtree Excel Programming 2 November 30th 06 05:22 PM
extract data from a text file Spike Excel Programming 6 June 3rd 06 06:44 PM
Extract data from an old Quattro file Chuck[_11_] Excel Programming 4 October 2nd 05 04:11 PM


All times are GMT +1. The time now is 08:53 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017