FAQ
In the process of moving from centos 4 to a centos 5 machine one
script stopped working. I distilled it down to this problem :


#! /usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect("dbi:Oracle:host=SSSS;sid=SSSS", 'XXXX',
'XXXX',
{RaiseError => 1,
AutoCommit => 0,
LongReadLen => 5000
}
) || die "Database connection not made:
$DBI::errstr";

my $q = qq/
SELECT
(2+3),
cursor(select 2+3 from dual),
cursor(select 3+3 from dual)
FROM table where rownum < 10
/;

my $sth = $dbh->prepare($q);
$sth->execute();
while ( my @d = $sth->fetchrow_array ) {
print join(" " , @d) . "\t";
while ( my @g = $d[1]->fetchrow_array ) {
print join(" " ,@g) . "\t";
}
while ( my @g = $d[2]->fetchrow_array ) {
print join(" " ,@g ). "\t";
}
print "\n";
}

no problem in sqlplus for this query in either machine.

On the server with DBI 1.58 , DBD::Oracle 1.19 and oracle instant
client 10.1 it works fine.

On the server with DBI 1.611 , DBD::Oracle 1.24b and oracle instant
client 11.1 it returns nothing, with no errors.

Use of DBI_TRACE returns nothing I can work with, it just stops on
the newer server:

-> prepare for DBD::Oracle::db
(DBI::db=HASH(0x1be48c80)~0x1be48c40 '
SELECT
(2+3),
cursor(select 2+3 from dual),
cursor(select 3+3 from dual)
FROM table where rownum < 10
') thr#1bb1a010
dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 5000)...
Described col 1: dbtype 2(NVARCHAR2), scale 0, prec 0, nullok 1, name
(2+3)
: dbsize 2, char_used 0, char_size 0, csid 0, csform
0(0), disize 171
fbh 1: '(2+3)' NULLable, otype 2-> 5, dbsize 2/172, p0.s0
Described col 2: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
0, prec 0, nullok 1, name CURSOR(SELECT2+3FROMDUAL)
: dbsize 8, char_used 0, char_size 0, csid 0, csform
0(0), disize 8
fbh 2: 'CURSOR(SELECT2+3FROMDUAL)' NULLable, otype 116-
116, dbsize 8/8, p0.s0
Described col 3: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
0, prec 0, nullok 1, name CURSOR(SELECT3+3FROMDUAL)
: dbsize 8, char_used 0, char_size 0, csid 0, csform
0(0), disize 8
fbh 3: 'CURSOR(SELECT3+3FROMDUAL)' NULLable, otype 116-
116, dbsize 8/8, p0.s0
cache settings DB Handle RowCacheSize=0,Statement Handle
RowCacheSize=0, OCI_ATTR_PREFETCH_ROWS=339,
OCI_ATTR_PREFETCH_MEMORY=0, Rows per Fetch=339, Multiple Row Fetch=On
dbd_describe'd 3 columns (row bytes: 18 max, 20 est avg,
cache: 0)
<- prepare= DBI::st=HASH(0x1be48ee0) at trek_cursor_query.pl line
33
-> execute for DBD::Oracle::st
(DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
rs_array_init:imp_sth->rs_array_size=2, rs_array_idx=0,
prefetch_rows=0, rs_array_status=SUCCESS
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at trek_cursor_query.pl line 34
-> fetchrow_array for DBD::Oracle::st
(DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
dbd_st_fetch 3 fields...



Removing one of the cursor statements makes it work on both machines!
I am out of ideas.

Any ideas are most appreciated!

Thanks,
LK

Search Discussions

  • Charles Jardine at Aug 5, 2010 at 8:46 am

    On 04/08/10 19:27, LK wrote:
    In the process of moving from centos 4 to a centos 5 machine one
    script stopped working. I distilled it down to this problem : [snip]
    my $q = qq/
    SELECT
    (2+3),
    cursor(select 2+3 from dual),
    cursor(select 3+3 from dual)
    FROM table where rownum < 10
    /;
    [snip]

    I am the author of the code in DBD::Oracle which supports nested cursors.
    I am trying to reproduce your problem.

    THE SQL statement quoted above cannot be exactly what you are using, because
    'table' is a reserved word. If I substitute 'dual' for 'table', I get the
    expected results.

    Can you let us know whether your problem can be reproduced with 'dual' as the
    FROM clause. If not, can you tell us what, if anything, is special about the
    actual FROM clause in your test.

    --
    Charles Jardine - Computing Service, University of Cambridge
    cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
  • Lokasu lokasu at Aug 5, 2010 at 12:41 pm
    Thank you for your reply. Indeed I meant any "table", sorry for the
    confusion .

    I also get the expected results with:

    SELECT (2+3), cursor(select 2+3 from dual), cursor(select 2+3 from
    dual) FROM dual where rownum < 10

    5 DBI::st=HASH(0x3bf9540) DBI::st=HASH(0x3d9ee70) 5 5

    If I do :

    select analysis_id from analysis where rownum < 10

    I get the expected rows.

    select analysis_id, cursor(select 2+3 from dual) from analysis where rownum
    < 10

    It works fine.

    BUT:

    select analysis_id, cursor(select 2+3 from dual), cursor(select 2+3 from
    dual) from analysis where rownum < 10

    Returns nothing with no errors. There is nothing special about this or any
    other table in this database.

    Many thanks,
    LK







    On Thu, Aug 5, 2010 at 4:46 AM, Charles Jardine wrote:
    On 04/08/10 19:27, LK wrote:
    In the process of moving from centos 4 to a centos 5 machine one
    script stopped working. I distilled it down to this problem : [snip]
    my $q = qq/
    SELECT
    (2+3),
    cursor(select 2+3 from dual),
    cursor(select 3+3 from dual)
    FROM table where rownum < 10
    /;
    [snip]

    I am the author of the code in DBD::Oracle which supports nested cursors.
    I am trying to reproduce your problem.

    THE SQL statement quoted above cannot be exactly what you are using,
    because
    'table' is a reserved word. If I substitute 'dual' for 'table', I get the
    expected results.

    Can you let us know whether your problem can be reproduced with 'dual' as
    the
    FROM clause. If not, can you tell us what, if anything, is special about
    the
    actual FROM clause in your test.

    --
    Charles Jardine - Computing Service, University of Cambridge
    cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
  • Charles Jardine at Aug 9, 2010 at 3:55 pm

    On 05/08/10 13:41, lokasu lokasu wrote:
    Thank you for your reply. Indeed I meant any "table", sorry for the
    confusion .

    I also get the expected results with:

    SELECT (2+3), cursor(select 2+3 from dual), cursor(select 2+3 from
    dual) FROM dual where rownum < 10

    5 DBI::st=HASH(0x3bf9540) DBI::st=HASH(0x3d9ee70) 5 5

    If I do :

    select analysis_id from analysis where rownum < 10

    I get the expected rows.

    select analysis_id, cursor(select 2+3 from dual) from analysis where rownum
    < 10

    It works fine.

    BUT:

    select analysis_id, cursor(select 2+3 from dual), cursor(select 2+3 from
    dual) from analysis where rownum < 10

    Returns nothing with no errors. There is nothing special about this or any
    other table in this database.

    Many thanks,
    LK
    Thank you for all the above. I have now managed to reproduce the problem.
    The outer table needs to have at least two rows.

    The significant difference seems to be the version of the Oracle client.
    On my platform, everything works if I use the Oracle 10 full client.
    I see your problem if I use the Oracle 11.2 instant client.
    The versions of everything else are the same.

    I am afraid that I have very little time to work on this, so a solution
    may not come quickly.

    --
    Charles Jardine - Computing Service, University of Cambridge
    cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
  • John Scoles at Dec 17, 2010 at 2:22 pm
    A little slow on this one and I apologize for that.

    Just closing things out for 1.28 version of DBD::Oracle and noticed this one
    hanging about.

    So I though I would give it a few tests

    I checked 1.24 and it fails but it seems to be fixed in 1.62 and in trunk

    So I will check this one off my list for 1.28

    Cheers
    John Scoles


    On Wed, Aug 4, 2010 at 2:27 PM, LK wrote:

    In the process of moving from centos 4 to a centos 5 machine one
    script stopped working. I distilled it down to this problem :


    #! /usr/bin/perl -w
    use strict;
    use DBI;

    my $dbh = DBI->connect("dbi:Oracle:host=SSSS;sid=SSSS", 'XXXX',
    'XXXX',
    {RaiseError => 1,
    AutoCommit => 0,
    LongReadLen => 5000
    }
    ) || die "Database connection not made:
    $DBI::errstr";

    my $q = qq/
    SELECT
    (2+3),
    cursor(select 2+3 from dual),
    cursor(select 3+3 from dual)
    FROM table where rownum < 10
    /;

    my $sth = $dbh->prepare($q);
    $sth->execute();
    while ( my @d = $sth->fetchrow_array ) {
    print join(" " , @d) . "\t";
    while ( my @g = $d[1]->fetchrow_array ) {
    print join(" " ,@g) . "\t";
    }
    while ( my @g = $d[2]->fetchrow_array ) {
    print join(" " ,@g ). "\t";
    }
    print "\n";
    }

    no problem in sqlplus for this query in either machine.

    On the server with DBI 1.58 , DBD::Oracle 1.19 and oracle instant
    client 10.1 it works fine.

    On the server with DBI 1.611 , DBD::Oracle 1.24b and oracle instant
    client 11.1 it returns nothing, with no errors.

    Use of DBI_TRACE returns nothing I can work with, it just stops on
    the newer server:

    -> prepare for DBD::Oracle::db
    (DBI::db=HASH(0x1be48c80)~0x1be48c40 '
    SELECT
    (2+3),
    cursor(select 2+3 from dual),
    cursor(select 3+3 from dual)
    FROM table where rownum < 10
    ') thr#1bb1a010
    dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
    dbd_describe SELECT (EXPLICIT, lb 5000)...
    Described col 1: dbtype 2(NVARCHAR2), scale 0, prec 0, nullok 1, name
    (2+3)
    : dbsize 2, char_used 0, char_size 0, csid 0, csform
    0(0), disize 171
    fbh 1: '(2+3)' NULLable, otype 2-> 5, dbsize 2/172, p0.s0
    Described col 2: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
    0, prec 0, nullok 1, name CURSOR(SELECT2+3FROMDUAL)
    : dbsize 8, char_used 0, char_size 0, csid 0, csform
    0(0), disize 8
    fbh 2: 'CURSOR(SELECT2+3FROMDUAL)' NULLable, otype 116-
    116, dbsize 8/8, p0.s0
    Described col 3: dbtype 116(SQLT_RSET OCI 8 cursor variable), scale
    0, prec 0, nullok 1, name CURSOR(SELECT3+3FROMDUAL)
    : dbsize 8, char_used 0, char_size 0, csid 0, csform
    0(0), disize 8
    fbh 3: 'CURSOR(SELECT3+3FROMDUAL)' NULLable, otype 116-
    116, dbsize 8/8, p0.s0
    cache settings DB Handle RowCacheSize=0,Statement Handle
    RowCacheSize=0, OCI_ATTR_PREFETCH_ROWS=339,
    OCI_ATTR_PREFETCH_MEMORY=0, Rows per Fetch=339, Multiple Row Fetch=On
    dbd_describe'd 3 columns (row bytes: 18 max, 20 est avg,
    cache: 0)
    <- prepare= DBI::st=HASH(0x1be48ee0) at trek_cursor_query.pl line
    33
    -> execute for DBD::Oracle::st
    (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
    dbd_st_execute SELECT (out0, lob0)...
    Statement Execute Mode is 0 (DEFAULT)
    rs_array_init:imp_sth->rs_array_size=2, rs_array_idx=0,
    prefetch_rows=0, rs_array_status=SUCCESS
    dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
    <- execute= '0E0' at trek_cursor_query.pl line 34
    -> fetchrow_array for DBD::Oracle::st
    (DBI::st=HASH(0x1be48ee0)~0x1be48e20) thr#1bb1a010
    dbd_st_fetch 3 fields...



    Removing one of the cursor statements makes it work on both machines!
    I am out of ideas.

    Any ideas are most appreciated!

    Thanks,
    LK






    --
    From the Pythian family to yours, Happy Holidays and all the best in 2011!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedAug 4, '10 at 6:27p
activeDec 17, '10 at 2:22p
posts5
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase