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!
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