Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

DBD::ORACLE - empty strings in result while there supposed to be values

by Ricardo (Initiate)
on Oct 10, 2007 at 10:03 UTC ( #643914=perlquestion: print w/ replies, xml ) Need Help??
Ricardo has asked for the wisdom of the Perl Monks concerning the following question:

Hi. Sorry if this turns out to be a newbie mistake, but I've run into an odd problem while using DBD::Oracle. For some reason, my sql statement results in empty strings. Instead of getting the value i want to see there is a "''," (please see below). The amount of columns (6 in this case) are correct. I'm using perl v5.8.8, DBD::Oracle v1.19, Oracle instantclient-basic - instantclient-devel and sqlplus. Does someone know what i'm doing wrong? How do i get the values correctly like in sqlplus?

When i manually do a query with help of sqlplus of the Oracle instant client it is ok (see below).

This is the result with the empty strings when using DBD::Oracle :

main::(db1.pl:47): my $result = $sth->dump_results;

'', '', '', '', '', ''
'', '', '', '', '', ''
'', '', '', '', '', ''
'', '', '', '', '', ''
'', '', '', '', '', ''
5 rows

This is the result when using sqlplus :

node1# ./sqlplus login/login@//ip:port/db

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 10 10:36:34 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Compatible Release 9.2.0.4.0
Oracle Rdb OCI Server Release 7.1.6.2.1 - Production, Level 1.7
Oracle Rdb SQL Release 7.1.4.0.0 - Production
SQL> select * from table;
table1 ta table3 t table5 table 6
------ -- ------ - ------ -------------
004400 10 075935 0 075935 1
004400 10 075938 0 075938 1
520020 71 127464 0 127464 1
004400 10 075939 0 075939 1
004400 10 075945 0 075945 1
5 rows selected.
SQL


This is the code i'm using :

#!/usr/bin/perl use DBI; use DBD::Oracle; $ENV{'LD_LIBRARY_PATH'} = '/home/httpd/perl/instantclient_11_1/'; #$ENV{'NLS_LANG'} = 'AMERICAN_AMERICA.UTF8'; #$ENV{'NLS_LANG'} = 'AMERICAN_AMERICA.AL32UTF8'; #$$ENV{'NLS_NCHAR'} = 'AL32UTF8'; #$ENV{'NLS_NCHAR'} = 'UTF8'; #$ENV{NLS_LANG}="AMERICAN_AMERICA.WE8MACROMAN8S"; #$ENV{'NLS_LANG'} = 'WE8ISO8859P1'; #$ENV{'NLS_LANG'} = 'WE8MSWIN1252'; #$ENV{'NLS_LANG'} = 'US7ASCII'; #$ENV{'NLS_LANG'} = 'UTF8'; #$ENV{'NLS_LANG'} = 'AL32UTF32'; $ENV{NLS_LANG} = 'american_america.we8iso8859p1'; my $host= my $sid= my $port= my $user = my $passwd = my $dbh = DBI>connect("dbi:Oracle:host=$host;port=$port;sid=$sid", +$user, $passwd) or die "Unable to connect: $DBI::errstr"; my $statement = 'SELECT * FROM table'; $sth = $dbh->prepare($statement); $sth->execute; my $result = $sth->dump_results;



Thank you in advance,

Ricardo


Comment on DBD::ORACLE - empty strings in result while there supposed to be values
Download Code
Re: DBD::ORACLE - empty strings in result while there supposed to be values
by derby (Abbot) on Oct 10, 2007 at 12:28 UTC

    Hmmm ... well I would think you're either not connecting to the same server (probably not) or you're not in the database you think your in. At a minimum, your sql could be failing but you're not checking the status of your execute. I would put some error checking in place or at least set RaiseError.

    my $dbh = DBI->connect( "dbi:Oracle:host=$host;port=$port;sid=$sid", $user, $passwd, {RaiseError => 1} );

    -derby
Re: DBD::ORACLE - empty strings in result while there supposed to be values
by dwhite20899 (Friar) on Oct 10, 2007 at 12:31 UTC
    I don't use Oracle, but I think you should be doing a LOT of error checking while you're developing. See the examples here for some ideas. And don't use "select *" - use the column names you want - using column names is more efficient and safer down the road.
Re: DBD::ORACLE - empty strings in result while there supposed to be values
by rdfield (Priest) on Oct 10, 2007 at 14:34 UTC
    Looks like you're connecting to Rdb rather than Oracle. I wouldn't have thought that was supported by DBI.

    BTW, you don't need

    use DBD::Oracle;
    as DBI handles this itself.

    rdfield

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://643914]
Approved by Corion
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2015-07-06 15:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (77 votes), past polls