Host command fails if there is a database connection

[astroboy]

Here's a real weird one. I can successfully invoke SQL*Plus (Oracle's SQL shell) from a Perl script. However, if I create a DBI connection first, it fails. The SQL script, test.sql:

select sysdate from dual;
exit

and the perl script:

#!/usr/local/bin/perl
use DBI;

my $dbh = DBI->connect('DBI:Oracle:', 'scott', 'tiger', {RaiseError =>
+ 1}) || die DBI::errstr;

my @cmd =  (
          'sqlplus',
          '-s',
          'SCOTT/tiger',
          '@test.sql'
        );

my $output = qx{@cmd};
my $retcode = $?;

if ($? == -1) {
    die "Error: $?: $!";
}

print $output;

which dies with:

Error: -1: No child processes at ./test.pl line 17.

However if I comment line 4, it works! Now it works for me on an older system. The differences are:

older system: Perl 5.10.0, DBI 1.609,DBD::Oracle.1.23, Centos 5.4, Oracle 11g release 1

new system: Perl 5.10.1, DBI 1.609, DBD::Oracle.1.23, Centos 5.4, Oracle 11g release 2

To try and eliminate Perl, I used the vendor version (5.8) on the new system. The only thing I can guess is that the version of Oracle makes a difference. But before I go to all of the trouble of reinstalling Oracle, I'm wondering if anyone can offer a reason for my problem, and a solution.

Note: the actual code in my app uses IPC::Cmd to handle the external command call (and deal with stderr and stdout, etc). I simply use qx{} the test case above to illustrate the unerlying problem. In the real code, the SQL script runs without error, but the return status is always -1

[mje]

The Oracle OCI libraries muck around with SIGCHILD which will affect running child processes (which you are when you run sqlplus like that). If you absolutely need to do what you are doing I think you need to reset SIGCHILD back to its default behavior.

Update: I believe you can stop the oracle libraries doing this by adding BEQUEATH_DETACH=YES to your sqlnet.ora file but I'm not sure of the other affects.

[astroboy]

Thanks for your reply. setting or unsetting BEQUEATH_DETACH had no effect for me, but going via a dedicated server process worked for me - thanks for putting me on the right track!

[roboticus]

astroboy:

I'm sorry I don't have the answer for you, hopefully the one provided by mje will save the day for you. I just wanted to give you a ++ for stating your problem so clearly.

...roboticus