Problem retrieving Number Data (Oracle)

From: Jason White (jewhite_at_umd.edu)
Date: 11/01/04 

To: dbi-users@perl.org
Date: Mon, 1 Nov 2004 16:13:42 -0500

All:

While I have some experience with the perl DBI, Oracle is new to me and
this current problem has me stumped.

Here's the client environment I'm working with ...
perl 5.6.1
DBI 1.42
DBD 1.06
SQL*Plus: Release 8.1.5.0.0
Oracle8i Enterprise Edition Release 8.1.7.4.50

In perl I do something like this ...
# already connected at this point ...
my $sql=q{select * from myDataView where fld1='value' and fld2 LIKE
'VALUE2%'};
my $seats;
my $sth = $dbh->prepare($sql);

while ($row_ref = $sth->fetchrow_hashref()) {
        ....
        my $STS_ISS = $row_ref->{STS_ISS};
        ...
}
# then I print the output out ...
        print "....
        STS_ISS - $STS_ISS
        ....";

All the other values come out OK .. and they are all VARCHAR2, but the
STS_ISS field is a NUMBER(3)

I get (what seems like) the raw binary back from the oracle sever ...
something like:
STS_ISS - ^V

Someone suggested I might need to 'unpack' the data from Oracle. I
tried a number of the standard unpack templates, but nothing seemed to
give me consistent data back although it did give me some 'numbers'
back.

OR

If I use sqlplus, it comes back fine. If anyone has a clue as to why
or how to get the correct data back, many thanks in advance. That, or
if someone has a good functioning shell script running sqlplus from
there and reading the output back. Most examples I've seen are Korn
shells ... not my strong point. Well ... the main hang up is setting
the oracle home from within ksh ... I'm more familiar with tcsh.

Again .. thanks in advance,

Jason

Relevant Pages

  • Re: ANNOUNCE: DBD:Oracle 1.18
    ... Well a warning in one compiler is better than and error in an other so I ... This might be a problem more with the older oracle OCI in your client. ... perl -Mblib t/26exe_array.t ...
    (perl.dbi.users)
  • Re: DBD Oracle 1.20 ORA-24334 Error
    ... On the premise instant client and also tried to run perl Makefile.PL -V ... the status after having connected to Oracle ... Failed 2/33 tests, 93.94% okay ...
    (perl.dbi.users)
  • Re: float bug? perl 5.8, DBI and oracle 10.2.0
    ... If you want to store high precision numbers in oracle, you've got 38 decimal digits to play with, and with minimal coaxing perl will handle them as strings at the appropriate points so that the exact values go in and come out. ... I haven't done any numerical work in 10 years or so, but I seem to recall that one can reasonably expect 6 or so decimal significant digits from a 32 bit floating point number - I'll go out on a limb and hazard that one can expect 12 or so digits from a 64 bit floating point number - at any rate I'd be very surprised to get 18 significant digits. ...
    (perl.dbi.users)
  • RE: Memory fault(coredump) with two DBD type connections
    ... I get a core dump ... all the libraries are the same threaded model(single threaded perl uses ... I am running Perl 5.8.8 with DBD-Oracle 1.19 compiled against Oracle ... when I try to connect to the same Oracle database AND same DB2 database ...
    (perl.dbi.users)
  • Re: suggestions sought on returning rows from oracle proc and deleting them in same proc
    ... this is not Perl stuff but Oracle PL/SQL. ... The reason it is particularly applicable to DBD::Oracle is that it would be possible to solve my problem very easily IF DBD::Oracle supported the returning of oracle table types without pipelining - it doesn't. ... I also needed the gathering of the ids and the deletion done in the same proc. ... create mytable (id int, created timestamp); ...
    (perl.dbi.users)
Loading