如何使用VB脚本连接多个oracle数据库并编写多个SQL查询 [英] How to connect multiple oracle databases using VB script and write multiple SQL query

问题描述

您好，



我需要从多个oracle数据库生成多个SQL查询的o / p。所以我想使用VB脚本连接excel到多个数据库并一次运行所有查询。



请帮助!!!这是我的脚本，它只连接到一个数据库，只运行一个没有标题的查询。如何连接多个数据库并运行多个查询。





代码：----

Hello,

I need to generate o/p of multiple SQL query from multiple oracle databases. So I want to connect from excel using VB script to multiple database and run all the query at a time.

Please help!!! Here is my script which will connect only to one database and run only one query without heading. How to make it possible to connect multiple database and run multiple query.


CODE :----

Sub Ora_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim query As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim UID As String
Dim PWD As String
Dim Server As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim strSQL As String
Dim strCon As String
Dim strResult As String
Dim OraDynaSet As Object
Dim i As Integer
Application.Calculation = xlCalculationManual
Set Data = Sheets("Sheet1") 'Change this to the name of the sheet you want to return data to
Data.Select
Range("A:F").ClearContents 'Change A:F to the range you expect data to return to
'---- Replace below highlighted names with the corresponding values
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=<server_name>)(PORT=<port_number>))" & _
"(CONNECT_DATA=(SID=<db_name>))); uid=user_name; pwd=password;"

'---  Open   the above connection string.
con.Open (strCon)
'---  Now connection is open and you can use queries to execute them.
'---  It will be open till you close the connection
Cmd.ActiveConnection = con
Cmd.CommandType = adCmdText

' Put your query next
sqlText = " select name from v$database "

Cmd.CommandText = sqlText
Set rs = Cmd.Execute
Do While Not rs.EOF 'this loops through the data and puts it in the spreadsheet
Row = Row + 1
For Findex = 0 To rs.Fields.Count - 1
Data.Cells(Row + 1, Findex + 1) = rs.Fields(Findex).Value
Next Findex
rs.MoveNext
Loop
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub

推荐答案

数据库

Cmd.CommandText = sqlText
设置 rs = Cmd.Execute
执行 while 不 rs.EOF ' 这会遍历数据并将其放入电子表格中
行=行+ 1
对于 Findex = 0 rs.Fields.Count - 1
Data.Cells（Row + 1 ，Findex + 1 ）= rs.Fields（Findex）.Value
下一步 Findex
rs.MoveNext
循环
Application.Calculation = xlCalculationAutomatic
Application.Calculate
结束 Sub

database " Cmd.CommandText = sqlText Set rs = Cmd.Execute Do While Not rs.EOF 'this loops through the data and puts it in the spreadsheet Row = Row + 1 For Findex = 0 To rs.Fields.Count - 1 Data.Cells(Row + 1, Findex + 1) = rs.Fields(Findex).Value Next Findex rs.MoveNext Loop Application.Calculation = xlCalculationAutomatic Application.Calculate End Sub

声明多个连接对象和多个记录集对象。



例如：

Declare multiple connection objects and multiple recordset objects.

E.g.:

Dim con1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim Cmd1 As New ADODB.Command
Dim con2 As ADODB.Connection
Dim rs2 As ADODB.Recordset
Dim Cmd2 As New ADODB.Command

为了您的理智，我建议您使用更有意义的名称（例如，conCustomers，cmdOrders）



使用类似每组连接/查询的代码。



注意：

如果有多个查询对于相同的连接，你可以重复使用相同的连接，每次都不需要新的连接。

如果每个查询的代码非常相似，你可以使代码更通用基于传递参数并从父子程序多次调用它（代码重用）。

For your sanity I suggest you use more meaningful names (e.g., "conCustomers", "cmdOrders")

Use similar code for each set of connections/queries.

Notes:
If there are multiple queries for the same connection, you can reuse the same connection, you' don't need a new one each time.
If the code is very similar for each query you could make the code more generic based on passed arguments and call it multiple times from a parent subroutine (code re-use).

这篇关于如何使用VB脚本连接多个oracle数据库并编写多个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！