Click here to Skip to main content
Click here to Skip to main content

Function to execute SQL script via Sql *Plus from VBA

By , 3 Mar 2010
Rate this:
Please Sign up or sign in to vote.
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)

About the Author

byapparov
Database Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 3 Mar 2010
Article Copyright 2010 by byapparov
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid