How to capture exit code when using open / print to execute an SQL statement?

Question

I use open / print to execute an SQL statement using sqlplus, for example:

open (PLSQL, "|sqlplus -s $db_url"); print PLSQL <<ENDSQL; ... some SQL statements ... exit; ENDSQL close(PLSQL); 

My question is how to grab the exit code to execute the sql statement if it encounters some errors. I think using DBI should be much better, but I prefer the solution to the problem above. Thank you very much!

Answer 1

close() should tell you what you want to know:

If the file descriptor comes from an open channel, closing returns false if one of the other involved system calls does not work, or if its program exits with a non-zero status. If the only problem was that the program completed a nonzero value, $! will be set to 0. Closing the pipe also awaits the completion of the process running on the pipe - in case you want to look at the output of this channel later - and implicitly puts the value of the output status of this command in $? and $ {^ CHILD_ERROR_NATIVE}.

The main thing is that close() will return false for any error, $! will be set only if syscall had an error, and $? The output status will be set. See Variable errors in perlvar for more details .

Answer 2

I have not seen SQLPlus return the correct exit codes for SQL statements, only things like refusing to connect or authenticate.

 echo "SELECT * FROM NO_EXIST;" | sqlplus64 -S USER/ [e-mail] /MYAPP ; echo $? SELECT * FROM NO_EXIST * ERROR at line 1: ORA-00942: table or view does not exist 0 

I highly recommend the library in the language if you can manage it. I couldn’t and would like to output ORA-\d\d\d\d\d\d as a failure indicator.

Hope this helps.

Answer 3

As you say, you would be much better off using the DBI module than crawling in sqlplus to process the database. The utility is intended only for the convenience of the command line, and not for any large operations with the database, and with the help of the module you are much better at controlling any errors that may occur, which, apparently, is the question of your question.

Perl does not allow you to connect to either the STDIN or the STDOUT process. For this you need to use the IPC::Open2 from CPAN. Read about the issue in Bidirectional communication with another process