Is there a .Net interface to Oracle SQLPLUS?

Hi,

I'm developing some automation to control the execution of SQL scripts. The scripts are run through SQLPLUS 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 SQLPLUS? If so has anyone used it?

Cheers Rich

From stackoverflow

• 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.

  Rich : I have already tried this, and the problem (as I can see you've encountered it too) is that sqlplus does not exit from WaitForExit - unless you shell execute it.

  Mac : Your SQL script should include an EXIT command at the end...

  Rich : ... the scripts already have an exit; at the end ... and WaitForExit still hangs forever.

• 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