Is there a .Net interface to Oracle SQLPLUS?

Question

I'm developing some automation to control the execution of SQL scripts. The scripts are run through SQL*PLUS and contain PL/SQL calls in them (hence I can't run them through ODP.NET).

I was wondering if there was a .NET interface to SQL*PLUS? If so has anyone used it?

Answer 1

You can do it in C# with this piece of code:

public int execString(string scriptFileName)
{
   int exitCode;
   ProcessStartInfo processInfo;
   Process process;
   int timeout = 5000;

   processInfo = new ProcessStartInfo("sqlplus.exe", "@" + scriptFileName);
   processInfo.CreateNoWindow = true;
   processInfo.UseShellExecute = false;
   process = process.Start(ProcessInfo);
   process.WaitForExit(timeout);
   exitCode = process.ExitCode;
   process.Close();

   return exitCode;
}

In VB.NET you could accomplish the exact same thing, using the same API in the framework, but I don't know much about VB.NET syntax.

You could also try inspecting SQL/Plus DLLs and see if you can get something out of them. But I think that even though it should be a faster (performance wise) approach, it will be way more complicated than using what I am suggesting.

Answer 2

It took me a while to figure out how to make it all work so here is the result of my investigations:

c# code:

ORAUtils.execString(@"c:\tmp.sql 'Oracle sucks!'");
...

using System.Diagnostics; -- where the Process stuff lives
...
public static int execString(string scriptFileName)
{
 ...
 ProcessStartInfo processInfo = new ProcessStartInfo();
 processInfo.FileName = "sqlplus.exe";
 processInfo.Arguments = "user/pwd@db @" + scriptFileName;
 ...
 Process process = Process.Start(processInfo); // typo in code above small p instead of caps helps
 ...

Resulting command line:

sqlplus.exe user/pwd@db @c:\tmp.sql 'Oracle sucks!'

Type sqlplus /? in a dos prompt and you'll get the syntax:

sqlplus

Here logon=user/pwd@db and start=@c:\tmp.sql 'Oracle sucks!'

It will start the sql file and pass it the parameter string.

tmp.sql first line:

prompt &1

will display the parameter string.

Thx