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

Question

I am using open/print to execute a SQL statement using sqlplus, like this:

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

My question is how to capture the exit code of executing the sql statement if it runs into some errors. I think using DBI should be much better, but I prefer to have a solution to the problem above. Thanks a lot!

Answer 1

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

If the filehandle came from a piped open, close returns false if one of the other syscalls involved fails or if its program exits with non-zero status. If the only problem was that the program exited non-zero, $! will be set to 0 . Closing a pipe also waits for the process executing on the pipe to exit--in case you wish to look at the output of the pipe afterwards--and implicitly puts the exit status value of that command into $? and ${^CHILD_ERROR_NATIVE}.

The main points are that close() will return false for any error, $! will be set only if a syscall had an error, and $? will be set to the exit status. See Error Variables in perlvar for details.

Answer 2

I've not seen SQLPlus return proper exit codes for SQL statements, only things like failure to connect or authenticate.

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


0

I highly recommend an in-language library, if you can manage it. I couldn't and so would grep output for ORA-\d\d\d\d\d\d as indication of failure.

Hope that helps.

Answer 3

As you say, you would be very much better off using the DBI module than shelling out to sqlplus to do the database manipulation. The utility is meant only as a command-line convenience and not for any major database operations, and using the module you are in far better control of handling any errors you might get, which seems to be the point of your question

Perl doesn't natively allow connecting to both the STDIN and STDOUT of a process. To do that you need to use the IPC::Open2 module from CPAN. Read about the problem in Bidirectional Communication with Another Process