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 |
All times are GMT +1. The time now is 06:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com