Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Function to execute SQL script via Sql *Plus from VBA

0.00/5 (No votes)
3 Mar 2010CPOL 1  
Introduction...
Introduction

I needed to execute parametrized SQL script with Sql *Plus from MS Access.

So I wrote this function that runs script in Sql *Plus with an option of passing parameters.

Limitations:
- Allows no more then 5 parameters
- Does not work correctly with parameters that contain space. This seems to be the limitation of the Sql *Plus in Windows environment rather then anything else.

Parameters of the function:
Path - Full path to the file with the Sql script.
Login - Oracle login string - login/password@dbname.
You can miss Login parameter if you are logging to the database in the script.
Param1-Param5 - Parameters that will be passed to the script. These are substitution variables in Sql *Plus and can be retrieved like this - &1;
The best thing to do is to redefine parameters in the script in the beginning of the script, i.e.:
DEFINE Var1=&1
DEFINE Var2=&2
DEFINE Var3=&3
DEFINE Var4=&4

This should be done due to a bug in Sql *Plus that does not work correctly if parameter &4 is used in the middle of script (I always get "be" instead of the correct value).

Function
Public Function RunScript(Path As String, _
                            Optional Login As String, _
                            Optional Param1 As String, _
                            Optional Param2 As String, _
                            Optional Param3 As String, _
                            Optional Param4 As String, _
                            Optional Param5 As String) As Boolean
    
    Dim cmdline As String
        
    On Error GoTo Err
        
    cmdline = "SqlPlus " ' Initiate cmdline
    
    ' add login part of the SqlPlus command
    If IsMissing(Login) Or Login = vbNullString Then
            cmdline = cmdline & "/nolog "
    Else:   cmdline = cmdline & Login
    End If
    
    ' Combine params in one string
    Dim params As String
    params = vbNullString
    If Not IsMissing(Param1) And Not Param1 = vbNullString Then                 '1
        If InStr(1, Param1, " ") Then Param1 = """" & Param1 & """"
        params = params & " " & Param1
        If Not IsMissing(Param2) And Not Param2 = vbNullString Then             '2
            If InStr(1, Param2, " ") Then Param2 = """" & Param2 & """"
            params = params & " " & Param2
            If Not IsMissing(Param3) And Not Param3 = vbNullString Then           '3
                If InStr(1, Param3, " ") Then Param3 = """" & Param3 & """"
                params = params & " " & Param3
                If Not IsMissing(Param4) Then       '4
                    params = params & " " & Param4
                    If Not IsMissing(Param5) Then   '5
                        params = params & " " & Param5
                    End If
                End If
            End If
        End If
    End If
    
    If Not params = vbNullString Then
        Path = Path & " " & params
    End If
    
     ' Append path of the script that we want to run
    cmdline = cmdline & "@" & Path
   
    
    Dim res As Long
    
    ' execute the command line
    ' See http://www.vbmonster.com/Uwe/Forum.aspx/vb/14063/VB6-and-Shell</a> 
    ' to get ExecCmd function.
    res = cmd.ExecCmd(cmdline)
    If res = 0 Then ' Finished OK
        RunScript = True
    Else ' Errored
        RunScript = False
    End If

Exit Function
Err:
    RunScript = False
   ' lgr.LogError Err.Number, Err.Description, "RunScript"
End Function

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)