如何通过ADODB从Excel调用无输入参数的Oracle存储过程？

You can absolutely call your parameterless Oracle stored procedure (with an output SYS_REFCURSOR) from Excel using ADODB—this approach aligns perfectly with your goal of restricting users to pre-defined procedures instead of letting them run arbitrary SQL. Here’s a step-by-step breakdown of how to implement it:

Prerequisites #

• Ensure the OraOLEDB.Oracle driver is installed on the machine running Excel (this is the standard Oracle OLE DB provider for ADODB).
• In Excel’s VBA editor, enable the ADODB library: Go to Tools > References and check Microsoft ActiveX Data Objects x.x Library (pick the latest version available, e.g., 6.1).

VBA Code Implementation #

Here’s a complete, tested script that connects to Oracle, calls your Get_Data procedure, and loads the results into Excel:

Sub FetchDataFromOracleSP()
    Dim dbConn As ADODB.Connection
    Dim dbCmd As ADODB.Command
    Dim resultRS As ADODB.Recordset
    Dim outputParam As ADODB.Parameter
    
    ' Enable error handling to catch connection/procedure issues
    On Error GoTo ErrorHandler
    
    ' 1. Initialize and open the database connection
    Set dbConn = New ADODB.Connection
    dbConn.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                              "Data Source=YourOracleTNSAlias;" & _
                              "User Id=YourOracleUsername;" & _
                              "Password=YourOraclePassword;"
    dbConn.Open
    
    ' 2. Configure the command object for the stored procedure
    Set dbCmd = New ADODB.Command
    Set dbCmd.ActiveConnection = dbConn
    dbCmd.CommandText = "Get_Data" ' Match your stored procedure name exactly
    dbCmd.CommandType = adCmdStoredProc ' Tell ADODB this is a stored procedure
    
    ' 3. Define the output cursor parameter
    ' Oracle's SYS_REFCURSOR maps to ADODB's adVariant type
    Set outputParam = dbCmd.CreateParameter("OUTPUT", adVariant, adParamOutput)
    dbCmd.Parameters.Append outputParam
    
    ' 4. Execute the procedure and retrieve the recordset
    dbCmd.Execute
    Set resultRS = outputParam.Value
    
    ' 5. Load results into Excel (starting at cell A1)
    If Not resultRS.EOF Then
        ' First, add column headers
        Dim colIndex As Integer
        For colIndex = 0 To resultRS.Fields.Count - 1
            Cells(1, colIndex + 1).Value = resultRS.Fields(colIndex).Name
            Cells(1, colIndex + 1).Font.Bold = True
        Next colIndex
        
        ' Then load the data below the headers
        Range("A2").CopyFromRecordset resultRS
    End If

Cleanup:
    ' Clean up resources to avoid memory leaks
    If Not resultRS Is Nothing Then
        If resultRS.State = adStateOpen Then resultRS.Close
        Set resultRS = Nothing
    End If
    Set dbCmd = Nothing
    If Not dbConn Is Nothing Then
        If dbConn.State = adStateOpen Then dbConn.Close
        Set dbConn = Nothing
    End If
    Exit Sub

ErrorHandler:
    MsgBox "Error encountered: " & Err.Description, vbExclamation
    GoTo Cleanup
End Sub

Key Notes #

• Connection String: Replace YourOracleTNSAlias, YourOracleUsername, and YourOraclePassword with your actual Oracle connection details. If you don’t use a TNS alias, you can use a direct connection string (e.g., Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=YourOracleServer)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=YourServiceName)));).
• Parameter Handling: The SYS_REFCURSOR output parameter is defined as adVariant because ADODB maps Oracle cursors to Variant types that resolve to a Recordset once the procedure runs.
• Security: Since users are only executing the pre-defined Get_Data procedure, they don’t need direct SELECT permissions on the ITEM_FILE table—only EXECUTE permission on the stored procedure, which is exactly what you wanted.

内容的提问来源于stack exchange，提问作者dan