Re: DBD::Oracle sometimes return wrong PRECISION

[H.Merijn Brand]

On Wed, 31 Dec 2014 16:58:02 +0100, "H.Merijn Brand"
<h.m.br...@xs4all.nl> 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  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.21   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/

pgp6dS65H8sK6.pgp
Description: OpenPGP digital signature