FAQ
Has anyone seen core dumps when disconnecting after closing an Oracle ref
cursor?

We're trying to use the new feature introduced in DBD::Oracle 1.05, as
specified in the Changes file:
Added ability to pass existing DBD::Oracle select statement handle
(cursor) back _into_ Oracle as a ref cursor type thanks to Mike Moretti.
Note that this enables a workaround for closing ref cursors:
$dbh->do("BEGIN CLOSE :cursor; END;", undef, $sth_ref_csr_to_close);
Everything appears to work fine until we attempt to disconnect from the
database, when we get a core dump from perl.

We haven't had any other problems or core dumps in "normal" DBI code.



Our versions are as follows:

dataserver:

Oracle 8.0.6.0.0 RDBMS, running on Solaris 2.6

client:

RedHat 6.2, Kernel 2.2.14-5.0

perl 5.005_03
DBI 1.14
DBD::Oracle 1.06


Our test package is defined as follows (note, this code is based on an
example provided by Geoffrey Young <gyoung@laserlink.net> from dbi-users).



--------------------------- BEGIN SQL code -----------------------
CREATE OR REPLACE PACKAGE curref_test
IS
TYPE cursor_reference IS REF CURSOR;

PROCEDURE emp_cursor (label_in IN VARCHAR2,
curref IN OUT cursor_reference);

PROCEDURE emp_cursor_close (curref IN cursor_reference);
END;
/


CREATE OR REPLACE PACKAGE BODY curref_test
IS

PROCEDURE emp_cursor (label_in IN VARCHAR2,
curref IN OUT cursor_reference)
IS
BEGIN
OPEN curref FOR
SELECT label_in FROM DUAL;
END;

PROCEDURE emp_cursor_close (curref IN cursor_reference)
IS
BEGIN
close curref;
END;

END;
/
--------------------------- END SQL code -----------------------


We have the following perl script to test this ...

############################ BEGIN PERL CODE ###################

#!/usr/bin/perl

use DBI;
use DBD::Oracle qw(:ora_types);

BEGIN {
## ENTER YOUR OWN VALUES HERE ...
$ENV{'ORACLE_HOME'}="/home/oracle/product/8.0.5";
$identifier = "dbname";
$user= "user";
$pass = "pass";
}

$label_in = "TEST STRING";

$dbh = DBI->connect("dbi:Oracle:$identifier", $user, $pass,
{RaiseError => 1, AutoCommit => 1, PrintError => 1}) || die $DBI::errstr;

print "Trying to open the ref cursor...\n";

$query = qq(
BEGIN
curref_test.emp_cursor(:label_in, :curref);
END;
);

$sth = $dbh->prepare($query);

$sth->bind_param(":label_in", $label_in);
$sth->bind_param_inout(":curref", \$curref, 0, {ora_type => ORA_RSET});

$sth->execute;
$sth->finish;

print "These are the results from the ref cursor:\n";

while (@result = $curref->fetchrow_array) {
print join "\t", @result, "\n";
}

print "Trying to close the ref cursor...\n";

$query = qq(
BEGIN
curref_test.emp_cursor_close(:curref);
END;
);

print "\tpreparing...\n";
$sth = $dbh->prepare($query);

print "\tbinding...\n";
$sth->bind_param(":curref", $curref, {ora_type => ORA_RSET});

print "\texecuting...\n";
$sth->execute;

print "\tfinishing...\n";
$sth->finish;

print "The cursor is now closed\n";

$dbh->disconnect;

############################ END PERL CODE ###################

When the above is run, we get the following output:


Trying to open the ref cursor...
These are the results from the ref cursor:
TEST STRING
Trying to close the ref cursor...
preparing...
binding...
executing...
finishing...
The cursor is now closed
Segmentation fault (core dumped)




setting DBI_TRACE to 2 produces the following just prior to the
coredump:

-> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x8152ccc)~0x80e6f48)
<- disconnect_all= '' at DBI.pm line 450.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x819f92c)~INNER)
<- DESTROY= undef during global destruction.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x8158020)~INNER)
Segmentation fault (core dumped)



strace produces the following output just prior to the coredump:

rt_sigaction(SIGINT, {SIG_DFL}, {0x402d8b60, ~[ILL ABRT BUS FPE KILL SEGV STOP XCPU XFSZ UNUSED], SA_RESTART|0x4000000}, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++


Removing the $dbh->disconnect line from the perl code causes the perl
to hang rather than coredump after the rt_sigprocmask calls/implicit
database handle close, but this still isn't very satisfactory.

Any suggestions or pointers gratefully received ... please reply by email,
to m.redington@ucl.ac.uk as well as to the list ...

cheers,
Martin

Search Discussions

  • Thomas A . Lowery at Mar 20, 2001 at 1:53 am
    I tested your script on solaris 2.7, Oracle 8.1.7, and perl 5.6.0.
    Works fine here. Does simply connecting and disconnecting (using DBI)
    work?

    Tom
    (environment listed below)

    [tlowery@rain tlowery]$ perl -w core.pl
    Trying to open the ref cursor...
    These are the results from the ref cursor:
    TEST STRING
    Trying to close the ref cursor...
    preparing...
    binding...
    executing...
    finishing...
    The cursor is now closed

    [tlowery@rain tlowery]$ uname -a
    SunOS rain.networkmcs.com 5.7 Generic_106541-12 sun4u sparc SUNW,Ultra-4
    [tlowery@rain tlowery]$ sqlplus /

    SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 19 20:48:04 2001

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    [tlowery@rain tlowery]$ perl -V
    Summary of my perl5 (revision 5.0 version 6 subversion 0) configuration:
    Platform:
    osname=solaris, osvers=2.7, archname=sun4-solaris
    uname='sunos golf.networkmcs.com 5.7 generic_106541-14 sun4u sparc sunw,ultra-4 '
    config_args=''
    hint=recommended, useposix=true, d_sigaction=define
    usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef
    useperlio=undef d_sfio=undef uselargefiles=define
    use64bitint=undef use64bitall=undef uselongdouble=undef usesocks=undef
    Compiler:
    cc='gcc', optimize='-O', gccversion=2.8.1
    cppflags='-I/usr/local/include'
    ccflags ='-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
    stdchar='char', d_stdstdio=define, usevfork=false
    intsize=4, longsize=4, ptrsize=4, doublesize=8
    d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
    ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
    alignbytes=8, usemymalloc=y, prototype=define
    Linker and Libraries:
    ld='gcc', ldflags =' -L/usr/local/lib '
    libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib
    libs=-lsocket -lnsl -ldl -lm -lc -lcrypt -lsec
    libc=/lib/libc.so, so=so, useshrplib=false, libperl=libperl.a
    Dynamic Linking:
    dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' '
    cccdlflags='-fPIC', lddlflags='-G -L/usr/local/lib'


    Characteristics of this binary (from libperl):
    Compile-time options: USE_LARGE_FILES
    Built under solaris
    Compiled at Jan 26 2001 09:42:37
    @INC:
    /usr/local/lib/perl5/5.6.0/sun4-solaris
    /usr/local/lib/perl5/5.6.0
    /usr/local/lib/perl5/site_perl/5.6.0/sun4-solaris
    /usr/local/lib/perl5/site_perl/5.6.0
    /usr/local/lib/perl5/site_perl/5.005/sun4-solaris
    /usr/local/lib/perl5/site_perl/5.005
    /usr/local/lib/perl5/site_perl
    .
    On Mon, Mar 19, 2001 at 04:47:03PM +0000, ucjtfmr wrote:

    Has anyone seen core dumps when disconnecting after closing an Oracle ref
    cursor?

    We're trying to use the new feature introduced in DBD::Oracle 1.05, as
    specified in the Changes file:
    Added ability to pass existing DBD::Oracle select statement handle
    (cursor) back _into_ Oracle as a ref cursor type thanks to Mike Moretti.
    Note that this enables a workaround for closing ref cursors:
    $dbh->do("BEGIN CLOSE :cursor; END;", undef, $sth_ref_csr_to_close);
    Everything appears to work fine until we attempt to disconnect from the
    database, when we get a core dump from perl.

    We haven't had any other problems or core dumps in "normal" DBI code.



    Our versions are as follows:

    dataserver:

    Oracle 8.0.6.0.0 RDBMS, running on Solaris 2.6

    client:

    RedHat 6.2, Kernel 2.2.14-5.0

    perl 5.005_03
    DBI 1.14
    DBD::Oracle 1.06


    Our test package is defined as follows (note, this code is based on an
    example provided by Geoffrey Young <gyoung@laserlink.net> from dbi-users).



    --------------------------- BEGIN SQL code -----------------------
    CREATE OR REPLACE PACKAGE curref_test
    IS
    TYPE cursor_reference IS REF CURSOR;

    PROCEDURE emp_cursor (label_in IN VARCHAR2,
    curref IN OUT cursor_reference);

    PROCEDURE emp_cursor_close (curref IN cursor_reference);
    END;
    /


    CREATE OR REPLACE PACKAGE BODY curref_test
    IS

    PROCEDURE emp_cursor (label_in IN VARCHAR2,
    curref IN OUT cursor_reference)
    IS
    BEGIN
    OPEN curref FOR
    SELECT label_in FROM DUAL;
    END;

    PROCEDURE emp_cursor_close (curref IN cursor_reference)
    IS
    BEGIN
    close curref;
    END;

    END;
    /
    --------------------------- END SQL code -----------------------


    We have the following perl script to test this ...

    ############################ BEGIN PERL CODE ###################

    #!/usr/bin/perl

    use DBI;
    use DBD::Oracle qw(:ora_types);

    BEGIN {
    ## ENTER YOUR OWN VALUES HERE ...
    $ENV{'ORACLE_HOME'}="/home/oracle/product/8.0.5";
    $identifier = "dbname";
    $user= "user";
    $pass = "pass";
    }

    $label_in = "TEST STRING";

    $dbh = DBI->connect("dbi:Oracle:$identifier", $user, $pass,
    {RaiseError => 1, AutoCommit => 1, PrintError => 1}) || die $DBI::errstr;

    print "Trying to open the ref cursor...\n";

    $query = qq(
    BEGIN
    curref_test.emp_cursor(:label_in, :curref);
    END;
    );

    $sth = $dbh->prepare($query);

    $sth->bind_param(":label_in", $label_in);
    $sth->bind_param_inout(":curref", \$curref, 0, {ora_type => ORA_RSET});

    $sth->execute;
    $sth->finish;

    print "These are the results from the ref cursor:\n";

    while (@result = $curref->fetchrow_array) {
    print join "\t", @result, "\n";
    }

    print "Trying to close the ref cursor...\n";

    $query = qq(
    BEGIN
    curref_test.emp_cursor_close(:curref);
    END;
    );

    print "\tpreparing...\n";
    $sth = $dbh->prepare($query);

    print "\tbinding...\n";
    $sth->bind_param(":curref", $curref, {ora_type => ORA_RSET});

    print "\texecuting...\n";
    $sth->execute;

    print "\tfinishing...\n";
    $sth->finish;

    print "The cursor is now closed\n";

    $dbh->disconnect;

    ############################ END PERL CODE ###################

    When the above is run, we get the following output:


    Trying to open the ref cursor...
    These are the results from the ref cursor:
    TEST STRING
    Trying to close the ref cursor...
    preparing...
    binding...
    executing...
    finishing...
    The cursor is now closed
    Segmentation fault (core dumped)




    setting DBI_TRACE to 2 produces the following just prior to the
    coredump:

    -> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x8152ccc)~0x80e6f48)
    <- disconnect_all= '' at DBI.pm line 450.
    -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x819f92c)~INNER)
    <- DESTROY= undef during global destruction.
    -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x8158020)~INNER)
    Segmentation fault (core dumped)



    strace produces the following output just prior to the coredump:

    rt_sigaction(SIGINT, {SIG_DFL}, {0x402d8b60, ~[ILL ABRT BUS FPE KILL SEGV STOP XCPU XFSZ UNUSED], SA_RESTART|0x4000000}, 8) = 0
    rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
    rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
    rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
    rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
    rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
    rt_sigprocmask(SIG_BLOCK, NULL, [], 8) = 0
    --- SIGSEGV (Segmentation fault) ---
    +++ killed by SIGSEGV +++


    Removing the $dbh->disconnect line from the perl code causes the perl
    to hang rather than coredump after the rt_sigprocmask calls/implicit
    database handle close, but this still isn't very satisfactory.

    Any suggestions or pointers gratefully received ... please reply by email,
    to m.redington@ucl.ac.uk as well as to the list ...

    cheers,
    Martin







    --
    Thomas A. Lowery stlowery@yahoo.com
    http://tlowery.hypermart.net

    _________________________________________________________
    Do You Yahoo!?
    Get your free @yahoo.com address at http://mail.yahoo.com
  • Ucjtfmr at Mar 21, 2001 at 10:52 pm

    I tested your script on solaris 2.7, Oracle 8.1.7, and perl 5.6.0.
    Works fine here. Does simply connecting and disconnecting (using DBI)
    work?

    Tom
    Thanks for trying it it for us Tom. Everything else appears to work fine
    with our versions.

    We've been using some older DBI and DBD versions in production for a couple
    of years, but we want to upgrade to take advantage of the ref cursor feature
    and the rowcache bugfix.

    Given that it works for you, I guess its something screwy with our perl
    build (homegrown) or environment.

    I'll try some different environments and see if I can get it to work ...

    cheers,
    Martin

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedMar 19, '01 at 4:51p
activeMar 21, '01 at 10:52p
posts3
users2
websitedbi.perl.org

2 users in discussion

Ucjtfmr: 2 posts Thomas A . Lowery: 1 post

People

Translate

site design / logo © 2022 Grokbase