Executing sqlplus.exe through Process / ProcessStartInfo and returning error code when PL SQL compile error
<p>I am executing sqlplus.exe through Process / ProcessStartInfo as below. When there is a SQL error, I get correct non zero exit code. But when there is a PL SQL compile error, I get 0 (success) as exit code.</p>

<p>How do I get non zero exit code when there is  PL SQL compile error. </p>

<p>I tried count from <code>user_errors</code>, but it is not reliable as there can be existing errors or if the same package failed again (for same reason), the error count does not change. </p>

<pre><code>public int Execute(string installFile, string instance, string userId, string password)
    {
        int exitCode;            
        ProcessStartInfo processInfo = new ProcessStartInfo();

        processInfo.WorkingDirectory = workingDirectory;
        processInfo.FileName = "sqlplus.exe";
        processInfo.Arguments = string.Format("{0}/{1}@{2} @{3}", userId, password, instance, installFile);
        processInfo.CreateNoWindow = false;
        processInfo.UseShellExecute = false;

        Process process = Process.Start(processInfo);
        process.WaitForExit();
        exitCode = process.ExitCode;
        process.Close();

        return exitCode;
    }
</code></pre>

<p>This is install SQL file - 1 (there can be more than 1 with same structure):</p>

<pre><code>WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE

SPOOL spool.txt append;

@@my_pkg.pks
@@my_pkg.pkb

EXIT SQL.SQLCODE
/
</code></pre>

<p>This is how I am calling the execute:</p>

<pre><code>foreach(var installFile in list of install files)
{
    exitCode = .Execute(installFile, instance, userId, password);

    if (exitCode != 0)
    {
        ....
        return -1;
    }
}
</code></pre>