Checking if entries exist in TNSNames.ora via VBScript
Hi All,
I have written a vbscript to append entries into the TNSNAMES.ora file. What I however want it to do is first see if the entries exist, if not, write the values in, If they do exist then not to.
Also how can I get the script to write the values in specific lines and columns?
I think I need the brain powers of my peers here.....help. Here's my script:
---------------------------------------------------
Const ForAppending = 8
Const Open_as_Unicode = True
strFolder = "C:\orant\NET80\ADMIN"
strFile = "TNSNAMES.ORA"
strPath = strFolder & "\" & strFile
Set FSO = CreateObject("Scripting.FileSystemObject")
If (FSO.FileExists(strPath) = True) Then
Set objFile = FSO.OpenTextFile(strPath, ForAppending, Open_as_Unicode)
objFile.Writeline("ASDBSRV.WORLD = ")
objFile.Writeline("(DESCRIPTION = ")
objFile.Writeline("(ADDRESS = (PROTOCOL = TCP)(HOST = trellisdb46)(PORT = 4444))")
objFile.Writeline("(CONNECT_DATA = ")
objFile.Writeline("(SID = TSR4PH1)")
objFile.Writeline("(SERVER = DEDICATED)")
objFile.Close : Set objFile = Nothing
End If
---------------------------------------------------------
Thank You
Calis
I have written a vbscript to append entries into the TNSNAMES.ora file. What I however want it to do is first see if the entries exist, if not, write the values in, If they do exist then not to.
Also how can I get the script to write the values in specific lines and columns?
I think I need the brain powers of my peers here.....help. Here's my script:
---------------------------------------------------
Const ForAppending = 8
Const Open_as_Unicode = True
strFolder = "C:\orant\NET80\ADMIN"
strFile = "TNSNAMES.ORA"
strPath = strFolder & "\" & strFile
Set FSO = CreateObject("Scripting.FileSystemObject")
If (FSO.FileExists(strPath) = True) Then
Set objFile = FSO.OpenTextFile(strPath, ForAppending, Open_as_Unicode)
objFile.Writeline("ASDBSRV.WORLD = ")
objFile.Writeline("(DESCRIPTION = ")
objFile.Writeline("(ADDRESS = (PROTOCOL = TCP)(HOST = trellisdb46)(PORT = 4444))")
objFile.Writeline("(CONNECT_DATA = ")
objFile.Writeline("(SID = TSR4PH1)")
objFile.Writeline("(SERVER = DEDICATED)")
objFile.Close : Set objFile = Nothing
End If
---------------------------------------------------------
Thank You
Calis
0 Comments
[ + ] Show comments
Answers (15)
Please log in to answer
Posted by:
anonymous_9363
15 years ago
I'd recommend a longer term policy for managing tnsnames, whether it be MSI, GP, or logon script, SMS or whatever it isGood point, Owen. It seems to me that a centrally-managed, network-located file is the way to go. I can't recall any major site I've worked at which uses local copies.
Posted by:
aogilmor
15 years ago
glad it worked out for you.
No need to pay us, just give ratings!!![:D]
No need to pay us, just give ratings!!![:D]
Posted by:
aogilmor
15 years ago
Here's a link for the hosts file script on this site maybe you can use that. I came up with a way a while back to just suck up a txt file and append it to another text file if I can find that I'll post the code or txt file
Posted by:
aogilmor
15 years ago
Here is some code I quickly modified so I could test, modify for your needs. Advantage over your way (IMHO) is that you can have a calltns.txt file with your oracle entries in it (see attachement) and it'll preserve the formatting and add multiple lines without modifying the script proper. You'll probably want to put some more error checking in, etc. Also FYI, the formatting in your Oracle entry looked incorrect. I've included the example I got from oracle in a text file with what it looks like your entries should be. Let me know if this works for you. Oh, and if you really need to check on the string you'll have to add code to condition the second loop on NOT finding a string in tnsnames.ora equal to "ASDBSRV.WORLD " or check the entire connect string after the first loop.
So now I've proven I'm a clever coder [:D] however, I'd recommend a longer term policy for managing tnsnames, whether it be MSI, GP, or logon script, SMS or whatever it is. I'd use this script for a one off in a group, although modified witih error checking and some removal code it COULD be used as part of a logon script or whatever to manage your tnsnames file.
So now I've proven I'm a clever coder [:D] however, I'd recommend a longer term policy for managing tnsnames, whether it be MSI, GP, or logon script, SMS or whatever it is. I'd use this script for a one off in a group, although modified witih error checking and some removal code it COULD be used as part of a logon script or whatever to manage your tnsnames file.
Dim arrFileLines()
CONST ForReading = 1
CONST ForWriting = 2
CONST ForAppending = 8
i = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileSrc = objFSO.OpenTextFile("c:\windows\calltns.txt", ForReading)
If objFSO.FileExists("c:\windows\tnsnames.ora") Then
objFSO.CopyFile "c:\windows\tnsnames.ora", "c:\windows\tnsnames.bak", True
End If
Set objFileDst = objFSO.OpenTextFile("c:\windows\tnsnames.ora", ForAppending, True)
Do Until objFileSrc.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFileSrc.ReadLine
i = i + 1
Loop
objFileSrc.Close
For l = LBound(arrFileLines) to Ubound(arrFileLines)
' take out to Step -1
objFileDst.WriteLine arrFileLines(l)
Next
objFileDst.Close
Posted by:
caliself
15 years ago
Guess what just as I was about to pluck my last remaining hair in frustration they decided the management finally decided to listen to me and have it centrally managed. If only the ****ers listened int he first place.
I can however apply the scrip tto another package we need
AOGilmor & VBScab I really want to thank you guys for all the hellp and hope one day I can obtain even a tenth of the skills you have
I can however apply the scrip tto another package we need
AOGilmor & VBScab I really want to thank you guys for all the hellp and hope one day I can obtain even a tenth of the skills you have
Posted by:
aogilmor
15 years ago
don't re-invent the wheel, there are already scripts that do this. IIRC I saw one on this site.
Posted by:
caliself
15 years ago
I don't want to re-invent the wheel I like the roundness of the current design. I however did look and couldn't find it on this site. Would you perchance know where it is?
Thanks
Thanks
Posted by:
aogilmor
15 years ago
i'll see if i can dig it up somewhere but meantime look here on old oracle packaging threads I think it was just included in one of the threads, not in the scripting forum.
Posted by:
caliself
15 years ago
I had a look through the oracle stuff there's nothing in there so if u can find it that would be great in the meantime does anyone have any ideas to my original question
Thanks
Thanks
Posted by:
anonymous_9363
15 years ago
There are hundreds of examples around which detail how to read and write text files. The Windows 'HOSTS' file is a good example of such a file. Find one of those and adapt it, if you can't locate the Oracle example.
Also, I'd have the script copy the original to a back-up first, make a temporary copy of the file to work on, manipulate the temporary file as required, then validate your changes. Only after that last step should your script then copy the temporary to TNSNAMES.ORA. As ever, always assume that the worst WILL happen and code accordingly.
EDIT:
Thought your script looked familiar... :) http://www.tech-archive.net/Archive/Scripting/microsoft.public.scripting.wsh/2005-05/msg00086.html
Also, I'd have the script copy the original to a back-up first, make a temporary copy of the file to work on, manipulate the temporary file as required, then validate your changes. Only after that last step should your script then copy the temporary to TNSNAMES.ORA. As ever, always assume that the worst WILL happen and code accordingly.
EDIT:
Thought your script looked familiar... :) http://www.tech-archive.net/Archive/Scripting/microsoft.public.scripting.wsh/2005-05/msg00086.html
Posted by:
anonymous_9363
15 years ago
It works, as far as it goes, but you require extra functionality. My point was, if you find a script which adds/removes entries from, say, HOSTS, you could adapt the code to suit.
Posted by:
caliself
15 years ago
I know and I am looking for one, can't find one yet......I won't ask you to write me one for two reasons
- I am new to scripting and wan to learn for myself
- I know you by reputation and have heard gruesome rumours about what you do to people who do ask
- I am new to scripting and wan to learn for myself
- I know you by reputation and have heard gruesome rumours about what you do to people who do ask
Posted by:
anonymous_9363
15 years ago
"Gruesome"? Nice...
In time, you'll come to know that I help those who have at least attempted to help themselves. The ones who get both barrels are those who post questions like "I need a script to update the registry on 'x' workstations. Today would be good."
As for examples, search my posts in 'Package Development' and 'Scripting' for the link http://www.computerperformance.co.uk/vbscript/index.htm. One of the posts containing it will contain several others, all of which will have examples. If that search turns up blank (unlikely), try http://cwashington.netreach.net
In time, you'll come to know that I help those who have at least attempted to help themselves. The ones who get both barrels are those who post questions like "I need a script to update the registry on 'x' workstations. Today would be good."
As for examples, search my posts in 'Package Development' and 'Scripting' for the link http://www.computerperformance.co.uk/vbscript/index.htm. One of the posts containing it will contain several others, all of which will have examples. If that search turns up blank (unlikely), try http://cwashington.netreach.net
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.