Repost from CPAN DBD::ORacle forum

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?

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-1 '', '', '', '', '', '' '', '', '', '',
'', '' '', '', '', '', '', '' '', '', '', '', '', '' '', '', '', '', '', ''
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

Re: Repost from CPAN DBD::ORacle forum

------=_Part_10600_6984372.1192025313165
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 10/10/07, John Scoles wrote:
>
> 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.


[...snip...]

This is the result with the empty strings when using DBD::Oracle :
> main::(db1.pl:47):
>
> my $result = $sth-1 '', '', '', '', '', '' '', '', '',
> '',
> '', '' '', '', '', '', '', '' '', '', '', '', '', '' '', '', '', '', '',
> ''
> 5 rows
>
>
> SQL this is the code i'm using :
> #!/usr/bin/perl use DBI;


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

You could waste the one argument you're allowed on -MDBI to load the DBI
module.

use DBD::Oracle;


If it is working at all, I assume the 'use DBI' on the #! line 'works'; you
should be using:

use DBI;

here instead.


$ENV{'LD_LIBRARY_PATH'} =
> '/home/httpd/perl/instantclient_11_1/';


[...11 comments omitted..]

$ENV{NLS_LANG} = 'american_america.we8iso8859p1';
> my $host=
> my $sid=
> my $port=
> my $user =
> my $passwd =



I assume there's a bunch of initializers not shown...

my $dbh = DBI>connect("dbi:Oracle:host=$host;port=$port;sid=$sid", $user,
> $passwd) or die "Unable to connect: $DBI::errstr";



There's a typo above: DBI->connect

my $statement = 'SELECT * FROM table';
> $sth = $dbh->prepare($statement);



There's no evidence here of checking whether the prepare succeeded.

$sth->execute;



There's no evidence here of checking whether the execute succeeded.

my $result = $sth->dump_results;


There's evidence here that some error checking earlier might help.

Put { RaiseError => 1 } as the 4th argument to your DBI->connect.


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0914 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_10600_6984372.1192025313165--