I have a small vbscript to load data and process it using sqlldr and sqlplus. I have 2 questions about sqlplus usage though:
1) Can I exec a stored procedure without using an .sql script file? e.g. sqlplus user/pass@server @exec proc_myname
2) Can I use .sql script files on a shared UNC path? e.g. sqlplus user/pass@server @serverpathscript.sql
I’ve tried playing around and at the moment am working around the problem by using a local temp directory to store the sql files. But I’m curious if there’s another/better way.
Thanks
Advertisement
Answer
Can I exec a stored procedure without using an .sql script file?
Yes. I don’t know VisualBasic good enough but the basic principle is that you create the child process for sqlplus and then send the commands via stdin (i.e. you have your script write to standard input of the child process).
Can I use .sql script files on a shared UNC path?
If the path is correct, then that should work. You can also try I/O redirection:
sqlplus user/pass@server < \serverpathscript.sql
The drawback of this approach is that the error messages won’t include the .sql script name.