On Wed May 18 06:53:40 2011, tim.vanholder@anubex.com wrote:
Show quoted text> I did some follow-up tests.
>
> These work:
OK, we are both guilty of not using strict and warnings:
BTW, your examples don't work for me. I get "Can't bind unknown
placeholder 'myparam'" presumably because that should be ":myparam" in
the bind_param_inout call.
Show quoted text> perl -le '
> use DBI;
> my $v;
> my $h = DBI->connect("dbi:Oracle:xxx","xxx","xxx");
> my $s = $h->prepare(q{begin :myparam := 'Hello World'; end;});
> $s->bind_param_inout('myparam', \$v, 4000, { TYPE => ORA_VARCHAR2 });
> $s-> execute;
> print "result: [$v]\n";
> '
If you add strict you'll see
Bareword "ORA_VARCHAR2" not allowed while "strict subs" in use
I'm not sure right now what TYPE gets set to if you use ORA_VARCHAR2
without importing it first (probably 0 which probably causes DBD::Oracle
to use the default type).
The following one should say:
Argument "ORA_VARCHAR2" isn't numeric
It needs to be numeric not a string 'ORA_VARCHAR2'. However because you
have not got strict or warnings enabled you do not see that and I guess
the default type is chosen.
Always use strict and warnings when you get strange results.
Show quoted text> perl -le '
> use DBI;
> use DBD::Oracle qw(:ora_types);
> my $v;
> my $h = DBI->connect("dbi:Oracle:xxx","xxx","xxx");
> my $s = $h->prepare(q{begin :myparam := 'Hello World'; end;});
> $s->bind_param_inout('myparam', \$v, 4000, { TYPE => 'ORA_VARCHAR2'
});
Show quoted text> $s-> execute;
> print "result: [$v]\n";
> '
Show quoted text>
> (reporting [Hello World]), but these do not:
> perl -le '
> use DBI;
> use DBD::Oracle qw(:ora_types);
> my $v;
> my $h = DBI->connect("dbi:Oracle:xxx","xxx","xxx");
> my $s = $h->prepare(q{begin :myparam := 'Hello World'; end;});
> $s->bind_param_inout('myparam', \$v, 4000, ORA_VARCHAR2);
> $s-> execute;
> print "result: [$v]\n";
> '
The above one is correct systax and uses ORA_VARCHAR2. Like you I get a
large string of what appears to be spaces.
Show quoted text> perl -le '
> use DBI;
> use DBD::Oracle qw(:ora_types);
> my $v;
> my $h = DBI->connect("dbi:Oracle:xxx","xxx","xxx");
> my $s = $h->prepare(q{begin :myparam := 'Hello World'; end;});
> $s->bind_param_inout('myparam', \$v, 4000, { TYPE => ORA_VARCHAR2 });
> $s-> execute;
> print "result: [$v]\n";
> '
The above one is also correct syntax etc and I believe uses
ORA_VARCHAR2.
Show quoted text> (reporting [<lots of spaces>]).
You missed out the {ora_type => ORA_VARCHAR2} which I believe is the
correct way to do it.
Bare in mind some notes in the pod about different string padding
behaviour - search for ORA_VARCHAR2. Here are some excerpts:
ORA_VARCHAR2
Oracle clients using OCI 8 will strip trailing spaces and allow
embedded \0 bytes. Oracle clients using OCI 9.2 do not strip trailing
spaces and allow embedded \0 bytes. This is the normal default
placeholder type.
Padded Char Fields
Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder
values to the size of the CHAR. As the default placeholder type value in
DBD::Oracle is ORA_VARCHAR2 to access this behaviour you will have to
change the default placeholder type with "ora_ph_type" or placeholder
type for a particular call with "bind" in DBI or "bind_param_inout" in
DBI with "ORA_CHAR".
Show quoted text> So the ORA_VARCHAR2 does not seem to be processed correctly if it
comes
Show quoted text> from DBD::Oracle's exported values (is it being treated as CHAR
instead
Show quoted text> of VARCHAR2?).
>
> Then I found that I did not get the same issue with the same
> code/versions against a different database - and both databases are
the
Show quoted text> same Oracle version too.
> Further tests showed that the results were identical even on an old
> debian box with an Oracle 10.2 client and an old DBD::Oracle (1.19 I
think).
Show quoted text>
> Turns out this was cause by the database's NLS character set (the
> "failing" database has AL32UTF8 where the "working" one has
"WE8MSWIN1252").
Show quoted text> Setting NLS_LANG causes same results as with the non-UTF8 database
> (space-padded "Hello World").
>
> This still does not (to me) explain why:
> - I do get a value when using SQL_VARCHAR or a ORA_VARCHAR2 as a
string
Show quoted text> - I get space-padded results when ORA_VARCHAR2 is used as an import
> from DBD::Oracle
Hope this helps.
Martin
--
Martin J. Evans
Wetherby, UK
