Thursday 4 September 2008

Export data from oracle via vbscript

I need to export data from oracle so it will be possible for me to generate alert from that log file. I do this because we do not have permission to change anything in the database.
Be carefull about fields section, you must know how many columns in the table.


'Parameters
path="c:\path.txt"
strHost="servername"
strPort="1529"
strSID="oraclesid"
strUsername="user"
strPassword="password"
dquerry="select * from table"


'Filesystem Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Check if file exist
If objFSO.FileExists(path) Then
Set objFile = objFSO.OpenTextFile(path,8,True)
else
wscript.echo "there is no file I am creating"
set objFile = objFSO.createtextfile(path)
'set objFile = objFSO.OpenTextFile(path,8, True)
end If


'open connection to database
Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=" & strPort & "))" & _
"(CONNECT_DATA=(SID = " & strSID & "))); uid=" & strUsername & ";pwd=" & strPassword & ";"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon


Set oRs = oCon.Execute(querry)
While Not oRs.EOF
objFile.WriteLine(oRs.Fields(0).Value &"," & oRs.Fields(1).Value &"," & oRs.Fields(2).Value &"," & oRs.Fields(3).Value &"," & oRs.Fields(4).Value

&"," & oRs.Fields(5).Value &"," & oRs.Fields(6).Value &"," & oRs.Fields(7).Value &"," & oRs.Fields(8).Value &"," & oRs.Fields(9).Value &"," &

oRs.Fields(10).Value &"," & oRs.Fields(11).Value &"," & oRs.Fields(12).Value &"," & "Q3" &"," & "Q3" &"," & oRs.Fields(15).Value &"," & oRs.Fields(16).Value

)
oRs.MoveNext
Wend
oCon.Close
Set oRs = Nothing
Set oCon = Nothing

No comments: