如何使用VBScript执行Oracle SQL语句 [英] How to Execute an Oracle SQL Statements with VBScript

问题描述

我正在尝试通过Microsoft VBScript执行Oracle SQL语句或Oracle函数，并在结果集中循环或显示函数返回的值

I am trying to execute an Oracle SQL statement or Oracle Functions through Microsoft VBScript and the loop throught the result set or display the value returned by the function

到目前为止，我已经设法通过SQLPlus *连接到Oracle，但是现在我陷入了困境.有人可以帮忙吗?

So far I have managed to connect to Oracle via SQLPlus*, but now I am stuck. Can anybody help?

Dim output

Dim WshShell, oExec, input 

set WshShell = CreateObject("WScript.Shell")

set oEnv=WshShell.Environment("Process") 

cmdString = "C:\Oracle\11g\product\11.1.0\ruby\BIN\sqlplus.exe -S stradmin/stradmin@ruby select * from dual"
Set oExec = WshShell.Exec(cmdString) 


WScript.Echo "Status" & oExec.Status


Do While oExec.Status = 0 

     WScript.Sleep 2 

Loop 



input = "" 



Do While Not oExec.StdOut.AtEndOfStream 

          input = input & oExec.StdOut.Read(1) 

Loop 



wscript.echo input 

推荐答案

尝试执行此操作，应将记录集中的每个字段添加到输入字符串中.如果您只想从每条记录中获取特定值，则可以执行此操作

Try this it should add each field in the recordset to the input string. If you only want a specific value from each record you can do this

input = input & rs.Fields.Item("FIeld_Name")

而不是循环遍历每个字段.

Instead of looping though each field.

connectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=oracle_server;User Id=user;Password=password;"

Set connection = CreateObject("ADODB.Connection")

connection.Open connectionString
Set rs = connection.Execute("select * from dual")

input = ""

Do Until rs.EOF
    for i = 0  To rs.Fields.Count - 1
        input = input & rs.Fields.Item(i) & "|"
    Next
    input = input & VBNewLine
    rs.MoveNext
Loop

MsgBox input

Set connection = Nothing
Set rs = Nothing

这篇关于如何使用VBScript执行Oracle SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！