0

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/pass...@serverlocation.com " & 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.

Paul Sweatte
  • 24,148
  • 7
  • 127
  • 265
spences10
  • 570
  • 1
  • 13
  • 32

2 Answers2

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/password@serverlocation.com @%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 :)

spences10
  • 570
  • 1
  • 13
  • 32
0

Tried quoting the path?

WShell.Run "sqlplus username/pass...@serverlocation.com """ & _
             strFilePath & """"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Sorry I forgot to mention I tired that approach too with no success – spences10 Jan 31 '12 at 09:00
  • I saw that in your other question, but you didn't give much detail on what "no success" actually meant.... – Tim Williams Jan 31 '12 at 15:49
  • Hi Tim, no success means that I got the same error as before. So I get the error: "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;" I have found that I can do it via a bat file sqlplus username/password@serverlocation.com @"Z:\aFolder\aScript.sql" EXIT; – spences10 Feb 01 '12 at 11:30
  • Notice where the quote marks are, I have tried passing the same via Windows Script Host Object Model and get the same error WShell.Run "sqlplus username/pass...@serverlocation.com @" & _ Chr(34) & strFilePath & Chr(34) and WShell.Run "sqlplus username/pass...@serverlocation.com @" & _ Chr(34) & Chr(34) & strFilePath & Chr(34) & Chr(34) Still get the same error, looking at passing parameters to a .bat file now – spences10 Feb 01 '12 at 11:35