Click here to Skip to main content
12,892,320 members (48,087 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 3 Mar 2010

Function to execute SQL script via Sql *Plus from VBA

, 3 Mar 2010 CPOL
Rate this:
Please Sign up or sign in to vote.

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.

- 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).

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</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
    RunScript = False
   ' lgr.LogError Err.Number, Err.Description, "RunScript"
End Function


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


About the Author

Database Developer
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

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