Detecting error after a powershell statement is executed

Question

I am using powershell to run sqlplus and I would like PowerShell to detect if there are error after the script was run and to perform some action instead of me looking at the result file.

& 'sqlplus' 'system/myOraclePassword' '@Test' | out-file 'result.txt';

Normally in DOS, there is %errorlevel% when the command encounters error and I wonder if there is similar stuff in PowerShell?

Of course, I can read the log file myself but sometimes, thing got too routine and I may forget.

My Test.sql:

select level from dual
connect by level<5;
select 10/0 from dual;
quit;

There is clearly a division by zero error. The result.txt captures it but I would like powershell to detect it as well

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 27 16:24:30 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Apr 27 2017 16:17:34 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


     LEVEL
----------
     1
     2
     3
     4

select 10/0 from dual
         *
ERROR at line 1:
ORA-01476: divisor is equal to zero

Does the powershell statement return an errorlevel after the statement is executed like DOS?

I have tried:

& 'sqlplus' 'system/myOraclePassword' '@Test' | out-file 'result.txt';
if (errorlevel 1)
{ write-host error;
}
else
{ write-host ok;
}

But that has caused syntax error?

errorlevel : The term 'errorlevel' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

What is a proper way to check error in powershell?

UPDATE

I used this:

if ($LASTEXITCODE -ne 0 )
{ 
write-host error;
}
else
{ 
write-host ok;
}

Try replacing if (errorlevel 1) with if ($LASTEXITCODE -eq 1) — henrycarteruk
– henrycarteruk, Commented Apr 28, 2017 at 11:57
You will also need to add whenever sqlerror exit failure or whenever sqlerror exit 1 to the top of you SQL script, otherwise SQL*Plus will just exit normally when it gets to the quit and the shell won't know anything was wrong. But you can only really get success/failure, not the actual error (1476 here) - you can exit sql.sqlcode but most shells will wrap that at 256 which isn't helpful... — Alex Poole
– Alex Poole, Commented Apr 28, 2017 at 12:01
Possible duplicate of Invoke an exe from PowerShell and get feedback on success or failure — Mark Wragg
– Mark Wragg, Commented Apr 28, 2017 at 12:01
@AlexPoole: My apologies for coming back to your comment adding whenever sqlerror exit failure to the top of sql script. At first I thought it would solve the issue, but after looking more closely, $LASTEXITCODE is still 0 which meant it either has not received the error flag from the sql or sql does not consider a division by zero a sqlerror. Could you shed some more light on that for me? — user1205746
– user1205746, Commented Apr 28, 2017 at 19:10

Martin Brandl · Accepted Answer · 2017-04-28 11:58:10Z

4

Since you are invoking an executable, you probably want to check for the $LASTEXITCODE variable or the return value of sqlplus. In PowerShell each variable has a $ prefix.

answered Apr 28, 2017 at 11:58

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Jeff Zeitlin Over a year ago

It's probably worth noting that if all he's interested in is whether or not it "succeeded" (exited with a zero error code), rather than with the specific error code that it exited with, he can also test $?, which will be $true for a zero error code (success), and $false for a non-zero exit (failure).

user1205746 Over a year ago

@Martin Brandi: $LASTEXITCODE works but what value should I check against? and how do I capture the return value of sqlplus if sql itself throw out an error code?

Jeff Zeitlin Over a year ago

The values you check against are defined by the sqlplus command/program - check the documentation for that. It should also tell you what the return will be in the event of an SQL error.

user1205746 Over a year ago

@JeffZeitlin: true in my question but eventually, I would love to capture the error code from the sql script if possible

Jeff Zeitlin Over a year ago

@user1205746 - $LastExitCode captures the exit code of the most recently run external executable. If your SQL script is written to exit with a specific value in the event of a certain type of SQL error, that value will be found in $LastExitCode, just like it would have been in %ERRORLEVEL% in a classical batch script.

|

Collectives™ on Stack Overflow

Detecting error after a powershell statement is executed

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related