Catch sql-loader exception while executing sql-loader as command in perl

Question

We are executing sql-loader in perl using command like :
`sqlldr $db_arg control=$ctl data=$data log=$log bad=$bad`;

But in this way we are unable to find if sql-loader throws any exception.

For eg:- even if data has 30,000 records and a problem was encountered after loading 1,000 records , we only get a message that 1,000 records have been loaded.

Is there a way to find out if sql-loader has thrown any exception and what exception specifically.

Note : The counting the lines in data file is turning out to be quite expensive as the file has to be executed both in some old Windows Server as well as Unix servers so using `wc` is not an option.

Answer 1

Using back-ticks to run an external program is the recommended option if you want to capture whatever the program writes to STDOUT. If you're not capturing the data returned by the back-ticks (in a scalar variable or an array) then you are using the wrong tool for the job.

If you want to get the return value of the program, then you need to be using system() instead. But extracting the external program's actual return value from the value that you get back from system() takes a little work. The documentation for system() says this:

The return value is the exit status of the program as returned by the wait call. To get the actual exit value, shift right by eight (see below).

The "below" mentioned is presumably this code example (which actually checks $? rather than the value returned by system()):

if ($? == -1) {
    print "failed to execute: $!\n";
}
elsif ($? & 127) {
    printf "child died with signal %d, %s coredump\n",
        ($? & 127),  ($? & 128) ? 'with' : 'without';
}
else {
    printf "child exited with value %d\n", $? >> 8;
}