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