Passing SQLPlus script location using Windows Script Host Object Model in VBA

Question

Ok,

So I haz lots of scripts that are generated by a team in my organisation which need to then be processed via SQLPlus

Basically we/me get loads of emails with the script location @Z:/aFolder/aScript.sql;

We then have to process them individually by opening up SQLPlus and pasting the file location in there "@Z:/aFolder/aScript.sql;"

I have been looking into a way to automate this via either c# or VBA

I think that the Windows Script Host Object Model option via VBA is a good one

Example:

Option Explicit 

Sub SQLPlus(strFilePath) 

    Dim WShell As New WshShell 
    WShell.Run "sqlplus username/[email protected] " & strFilePath 

End Sub 

Sub test() 

    Call SQLPlus("@Z:/aFolder/aScript.sql;") 

End Sub 

Only issue is that I get an error from passing that last file location in there: "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;"

Am I missing a special character or something from this?

I pass the @ sign into the string but its not recognised on the command line??

Any input greatly appreciated, thanks.

Answer 1

Ok, this is now resolved [in a fashion]

What I'm doing is passing the file location parameter to a .bat file

So I have my .bat file OracleSQL.bat which contains this code:

@echo off 
sqlplus username/[email protected] @%1 
exit; 

I can now shell out to the batch file with the file parameter:

Dim strBatchName As String 

strBatchName = "C:\location\of\bat\OracleSQL.bat Z:/aFolder/aScript.sql" 
Shell strBatchName 

Done :)

Answer 2

Tried quoting the path?

WShell.Run "sqlplus username/[email protected] """ & _
             strFilePath & """"