Send SQL Commands to SQLPlus via Process in C#?-CodePudding

since apparently my other idea how to send multiple Statements to the Oracle DB was not so easy I have to do it differently...

I want to use SQLPlus over Process to issue all possible Oracle SQL Commands. I now have the problem that the Statements are never added to the Shell. Ie.: When this Code is run it will open the Shell and say "connected to: Oracle ...." and then show the "SQL>" prompt.

The result will be: "SP2-0042 Unknown command "test", which is okay. However when it wants to run the SQL query it only writes into the Shell "SQL> 2" and nothing happens. The DB is not changed either. If I add another Query it will write "3" into the same Line.

      ProcessStartInfo processInfo = new ProcessStartInfo();
        processInfo.FileName = "sqlplus.exe";
        processInfo.Arguments = "user/password@db";
        processInfo.CreateNoWindow = false;
        processInfo.UseShellExecute = false;
        processInfo.WorkingDirectory = Path.GetTempPath();
        processInfo.RedirectStandardInput = true;

        processInfo.RedirectStandardOutput = false;
        processInfo.RedirectStandardError = false;

        // Process process = Process.Start(processInfo);
        Process process = new Process();
        process.StartInfo = processInfo;

        //process.OutputDataReceived  = (sender,args) => { System.Diagnostics.Debug.WriteLine("ohh i got something"); };
        //process.ErrorDataReceived  = (sender, args) => { System.Diagnostics.Debug.WriteLine("ohh i got some error"); };

        process.Start();
        process.StandardInput.WriteLine(" test");
        Thread.Sleep(5000);


        process.StandardInput.WriteLine("create or replace view test(ID) as select ID from USER");

        Thread.Sleep(2000);
      process.StandardInput.Flush();
 
       // process.WaitForExit();
        process.Close();

I don´t see what is missing, I am doing everything like in all those Examples online. Btw, can I somehow get if an Command has already been executed or will I have to parse the DataReceived Event?

CodePudding user response：

The SQL> 2 you are seeing is because you are writing to the the SQL buffer, but you are not then executing that buffer. You would see the same thing if you manually typed that statement into the SQL*Plus client and hit return.

You need to end the SQL command.

You can do that to either change your statement to end with a semicolon:

process.StandardInput.WriteLine("create or replace view test(ID) as select ID from USER;");

or send a slash as a separate line:

process.StandardInput.WriteLine("create or replace view test(ID) as select ID from USER");
process.StandardInput.WriteLine("/");

Incidentally, USER is a built-in function and reserved word, so you can't have a table called that unless it's a quoted identifier - which it's usually better to avoid using.

Once it's working you might want to look at some of the set commands to modify the responses you see - you probably don't want to see the command echoed back to you for example, and might not want to see all feedback. You may also want to add the -s argument to hide the SQL*Plus banner etc.

(Also note that adding a semicolon is the opposite of advice that's often seen to avoid ORA-00933 or ORA-00911 errors; but that's because of how you are running this. SQL*Plus treats a semicolon as a statement separator and terminator and it causes execution to occur, but it is not part of the SQL buffer. With JDBC or a native .Net ODP DB call you can only execute one statement at a time and the semicolon is then irrelevant and illegal. With a direct call your current statement, without a semicolon, would be OK. Via SQL*Plus it is not...)