FAQ
I have something weird in DBD::Oracle (1.44 and 1.74)
Client = 10.2.0.1.0 / AIX-5.3 - Server = 11.2.0.3.0 - 64bit / Linux

consider a field that looks like this:

{ LINK => undef,
     NAME => 'D_GELD',
     NAME_lc => 'd_geld',
     NAME_uc => 'D_GELD',
     NULLABLE => '',
     PRECISION => 8,
     SCALE => 0,
     TYPE => 3,
     TYPE_NAME => 'DECIMAL',
     dbd_type => undef,
     ora_est_row_width => undef,
     ora_fail_over => undef,
     ora_lengths => 172,
     ora_rowid => undef,
     ora_type => undef,
     ora_types => 2
     }

*sometimes* I get 126 in the PRECISION entry instead of 8
does that ring any bells?

I get 126 only if I use a where clause that addresses a field that is
first in an index on that table otherwise I get 8

Search Discussions

  • H.Merijn Brand at Dec 31, 2014 at 5:49 pm

    On Wed, 31 Dec 2014 16:58:02 +0100, "H.Merijn Brand" wrote:

    I have something weird in DBD::Oracle (1.44 and 1.74)
    Client = 10.2.0.1.0 / AIX-5.3 - Server = 11.2.0.3.0 - 64bit / Linux

    consider a field that looks like this:

    { LINK => undef,
    NAME => 'D_GELD',
    NAME_lc => 'd_geld',
    NAME_uc => 'D_GELD',
    NULLABLE => '',
    PRECISION => 8,
    SCALE => 0,
    TYPE => 3,
    TYPE_NAME => 'DECIMAL',
    dbd_type => undef,
    ora_est_row_width => undef,
    ora_fail_over => undef,
    ora_lengths => 172,
    ora_rowid => undef,
    ora_type => undef,
    ora_types => 2
    }

    *sometimes* I get 126 in the PRECISION entry instead of 8
    does that ring any bells?

    I get 126 only if I use a where clause that addresses a field that is
    first in an index on that table otherwise I get 8
    I can reproduce it with this:

    --8<--- test.pl
    use 5.16.2;
    use warnings;

    use DBI;

    my $dbh = DBI->connect ("dbi:Oracle:", $ENV{DBI_USER}, $ENV{DBI_PASS}, {
         RaiseError => 1,
         PrintError => 1,
         AutoCommit => 1,
         ChopBlanks => 1,
         ShowErrorStatement => 1,
         FetchHashKeyName => "NAME_lc",
         });

    say "DBI: $DBI::VERSION";
    say "DBD: $DBD::Oracle::VERSION";
    say "OCI: ", ORA_OCI;
    say "Svr: @{$dbh->func ('ora_server_version')}";

    $dbh->do (qq;
         create table foo (
      a1 number (3) default -1,
      a2 number (4) default -1,
      a3 number (3) default -1,
      d_foo number (8) default -1,
      v_foo number (2) default 0,
      hist char (1) default 'A'
      ););

    sub prec
    {
         my $sth = $dbh->prepare (shift);
         $sth->execute;
         printf "PRECISION: %3d, St: %s\n", $sth->{PRECISION}[3], $sth->{Statement};
         } # prec

    sub test
    {
         prec ("select a1, a2, a3, d_foo, v_foo from foo");
         prec ("select a1, a2, a3, d_foo, v_foo from foo where a1 = 111");
         prec ("select a1, a2, a3, d_foo, v_foo from foo where a2 = 111");
         prec ("select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111");
         } # test

    test ();

    $dbh->do ("insert into foo values (101, 101, 25, 19841213, 0, 'A')");

    test ();

    $dbh->do ("create index idx_foo on foo (a1, a2, a3, d_foo desc, v_foo desc, hist)");

    test ();

    END { $dbh->do ("drop table foo"); }
    -->8---

    $ perl test.pl
    DBI: 1.632
    DBD: 1.74
    OCI: 11.2.0.3
    Svr: 11 2 0 3 0
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111


    SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 18:42:36 2014
    This is perl 5, version 20, subversion 0 (v5.20.0) built for x86_64-linux-thread-multi-ld
  • H.Merijn Brand at Jan 1, 2015 at 3:03 pm

    On Wed, 31 Dec 2014 18:49:24 +0100, "H.Merijn Brand" wrote:

    On Wed, 31 Dec 2014 16:58:02 +0100, "H.Merijn Brand"
    wrote:
    I have something weird in DBD::Oracle (1.44 and 1.74)
    Client = 10.2.0.1.0 / AIX-5.3 - Server = 11.2.0.3.0 - 64bit / Linux

    consider a field that looks like this:

    { LINK => undef,
    NAME => 'D_GELD',
    NAME_lc => 'd_geld',
    NAME_uc => 'D_GELD',
    NULLABLE => '',
    PRECISION => 8,
    SCALE => 0,
    TYPE => 3,
    TYPE_NAME => 'DECIMAL',
    dbd_type => undef,
    ora_est_row_width => undef,
    ora_fail_over => undef,
    ora_lengths => 172,
    ora_rowid => undef,
    ora_type => undef,
    ora_types => 2
    }

    *sometimes* I get 126 in the PRECISION entry instead of 8
    does that ring any bells?

    I get 126 only if I use a where clause that addresses a field that is
    first in an index on that table otherwise I get 8
    I can reproduce it with this:

    --8<--- test.pl
    use 5.16.2;
    use warnings;

    use DBI;

    my $dbh = DBI->connect ("dbi:Oracle:", $ENV{DBI_USER}, $ENV{DBI_PASS}, {
    RaiseError => 1,
    PrintError => 1,
    AutoCommit => 1,
    ChopBlanks => 1,
    ShowErrorStatement => 1,
    FetchHashKeyName => "NAME_lc",
    });

    say "DBI: $DBI::VERSION";
    say "DBD: $DBD::Oracle::VERSION";
    say "OCI: ", ORA_OCI;
    say "Svr: @{$dbh->func ('ora_server_version')}";

    $dbh->do (qq;
    create table foo (
    a1 number (3) default -1,
    a2 number (4) default -1,
    a3 number (3) default -1,
    d_foo number (8) default -1,
    v_foo number (2) default 0,
    hist char (1) default 'A'
    ););

    sub prec
    {
    my $sth = $dbh->prepare (shift);
    $sth->execute;
    printf "PRECISION: %3d, St: %s\n", $sth->{PRECISION}[3], $sth->{Statement};
    } # prec

    sub test
    {
    prec ("select a1, a2, a3, d_foo, v_foo from foo");
    prec ("select a1, a2, a3, d_foo, v_foo from foo where a1 = 111");
    prec ("select a1, a2, a3, d_foo, v_foo from foo where a2 = 111");
    prec ("select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111");
    } # test

    test ();

    $dbh->do ("insert into foo values (101, 101, 25, 19841213, 0, 'A')");

    test ();

    $dbh->do ("create index idx_foo on foo (a1, a2, a3, d_foo desc, v_foo desc, hist)");

    test ();

    END { $dbh->do ("drop table foo"); }
    -->8---

    $ perl test.pl
    DBI: 1.632
    DBD: 1.74
    OCI: 11.2.0.3
    Svr: 11 2 0 3 0
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 126, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111


    SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 18:42:36 2014
    This is perl 5, version 20, subversion 0 (v5.20.0) built for x86_64-linux-thread-multi-ld
    SQLite (all undef):
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: -, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

    MySQL:
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

    Unify:
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 9, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111

    CSV: (use integer instead of numeric)
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a1 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where a2 = 111
    PRECISION: 8, St: select a1, a2, a3, d_foo, v_foo from foo where d_foo = 111
    Can't find column definitions! at /pro/lib/perl5/site_perl/5.20.0/SQL/Statement.pm line 88.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-dev @
categoriesperl
postedDec 31, '14 at 3:58p
activeJan 1, '15 at 3:03p
posts3
users1
websitedbi.perl.org

1 user in discussion

H.Merijn Brand: 3 posts

People

Translate

site design / logo © 2019 Grokbase