Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 28811
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: champoux [...] pythian.com
Requestors: michael.virnstein [...] brodos.de
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.19
Fixed in: (no value)



Subject: Problem with CHAR-Out-Parameter and 10g
Download (untitled) / with headers
text/plain 780b
Perl: 5.8.8 Oracle: 10g Release 2 (10.2.0.3.0) Oracle-Client: 10g Client Release 2 (10.2.0.1.0) DBI: 1.58 DBD::Oracle: 1.19 When i want to get the value of an out-parameter from a procedure with bind_param_inout directly and the out-parameter is of type char, the value isn't returned correctly with Oracle10g. The same script worked with Oracle8i. create these procedures on your database: ---------------------------------------------------------- create or replace procedure char_test(char_value out char) is begin char_value := '1'; end; / create or replace procedure varchar2_test(varchar2_value out varchar2) is begin varchar2_value := '1'; end; / ---------------------------------------------------------- then use the attached script, and you'll see the problem
Subject: chartest.pl
Download chartest.pl
text/x-perl 926b
#!/usr/local/bin/perl use DBI; my $dbname = 'first10g'; my $dbuser = 'tbo'; my $dbpass = 'tbo'; my $query = q{declare char_value char(1); varchar2_value varchar2(1); begin char_test(:a); char_test(char_value); :b := char_value; varchar2_test(:c); varchar2_test(varchar2_value); :d := varchar2_value; end;}; my $dbh = DBI->connect('DBI:Oracle:'.$dbname, $dbuser, $dbpass, {AutoCommit => 0, RaiseError => 0}); my $sth = $dbh->prepare($query); my $a; my $b; my $c; my $d; $sth->bind_param_inout(':a', \$a, 1); $sth->bind_param_inout(':b', \$b, 1); $sth->bind_param_inout(':c', \$c, 1); $sth->bind_param_inout(':d', \$d, 1); $sth->execute(); print "a: \"$a\"\n"; print "b: \"$b\"\n"; print "c: \"$c\"\n"; print "d: \"$d\"\n"; $dbh->commit(); exit 0;
From: michael.virnstein [...] brodos.de
Is there any progress on this issue or won't it get fixed?
From: PYTHIAN [...] cpan.org
Download (untitled) / with headers
text/plain 662b
"When i want to get the value of an out-parameter from a procedure with bind_param_inout directly and the out-parameter is of type char, the value isn't returned correctly with Oracle10g. The same script worked with Oracle8i. " there are massive differenced btween the way 10g and 8i handaled chars and varchars? so this may be your problem. 8i didn't strip spaces from varchars while later version or Oracle will. I tried to run your script but all I get is a: "1 " b: "1" c: "1" d: "1" I take it you do not like what is happing in a:? or is you problem with b:,c:,or d: what do you get in 8i and what do you get with 10g
Download (untitled) / with headers
text/plain 239b
opps I forgot to pre my output my a: I get a: "1           & nbsp;           & nbsp;       "
Download (untitled) / with headers
text/plain 136b
crap I hate computers the a: value is 1 with 31 spaces to get 32 chars alltogether. <pre>a:="1 "</pre>
From: michael.virnstein [...] brodos.de
Download (untitled) / with headers
text/plain 308b
On Mi. 07. Mai 2008, 07:42:26, PYTHIAN wrote: Show quoted text
> crap I hate computers > > the a: value is > > 1 with 31 spaces to get 32 chars alltogether. > > <pre>a:="1 "</pre>
same for me. With 8i, i get the same output for all 4, but with 10g, the "a" value is right padded with spaces
From: michael.virnstein [...] brodos.de
Download (untitled) / with headers
text/plain 622b
On Mi. 07. Mai 2008, 08:46:43, michael.virnstein@brodos.de wrote: Show quoted text
> On Mi. 07. Mai 2008, 07:42:26, PYTHIAN wrote:
> > crap I hate computers > > > > the a: value is > > > > 1 with 31 spaces to get 32 chars alltogether. > > > > <pre>a:="1 "</pre>
> > > same for me. With 8i, i get the same output for all 4, but with 10g, the > "a" value is right padded with spaces
i don't have a 8i available anymore, but i know that all values were just "1" without any padded spaces and that the "a" value was the problem after the migration to 10g, because it suddently got right padded with spaces.
Download (untitled) / with headers
text/plain 914b
On Wed May 07 08:50:41 2008, michael.virnstein@brodos.de wrote: Show quoted text
> On Mi. 07. Mai 2008, 08:46:43, michael.virnstein@brodos.de wrote:
> > On Mi. 07. Mai 2008, 07:42:26, PYTHIAN wrote:
> > > crap I hate computers > > > > > > the a: value is > > > > > > 1 with 31 spaces to get 32 chars alltogether. > > > > > > <pre>a:="1 "</pre>
> > > > > > same for me. With 8i, i get the same output for all 4, but with
10g, the Show quoted text
> > "a" value is right padded with spaces
> > i don't have a 8i available anymore, but i know that all values were > just "1" without any padded spaces and that the "a" value was the > problem after the migration to 10g, because it suddently got right > padded with spaces.
So your expected result is all the same but you are not getting it> I will have a look into a litte deeper. Seems we have come accross this issues before but not with procedures cheers
From: michael.virnstein [...] brodos.de
Download (untitled) / with headers
text/plain 327b
On Mi. 07. Mai 2008, 11:05:14, PYTHIAN wrote: Show quoted text
> Seems we have come accross this issues before but not with procedures
it is the same for plsql functions that return a char. it seems to be related to the fact, that the actual length of the variable is not known. That's why "b" is correct, because the length is know to be "1".
Download (untitled) / with headers
text/plain 925b
I have just checked in a fix for this. What will happend now is char(s) will be set to the size of the buffer spefied in the bind so you code will now work with $sth->bind_param_inout(':a', \$a,1,{ ora_type => ORA_CHAR } ); This is of course more of a work around rather tham 100% solution. We have to gice DBD::Oracle a hint of what to expect from the DB, in this case a char of 1, otherwise is just makes it a varchar. Like you said in your last post it is more a problem on Oracle not sending back what type of data it is when one uses a function or procedure in this way. In a rather obtuse way this make sense as track.char_test(:a) is not really a data type it is just a pointer to a where to find the data. I could (me thinks) get the attributs of this pointer but that would require a number of other round trip calls to the DB so it may slow things down a bit. anyway I hope this works for you.
From: michael.virnstein [...] brodos.de
Download (untitled) / with headers
text/plain 713b
On Mi. 21. Mai 2008, 13:55:47, PYTHIAN wrote: Show quoted text
> This is of course more of a work around rather tham 100% solution. We > have to gice DBD::Oracle a hint of what to expect from the DB, in this > case a char of 1, otherwise is just makes it a varchar.
I think it is a valid solution, that i only get as much bytes as i request, instead of the of all data in the buffer. I don't now though, if this can be a problem with UTF-8 or other mulit-byte character sets, when the return values is a multi-byte character. But this seems to be more of a general problem. A solution would probably be, to allow DBD::Oracle to switch between byte- and char-mode when binding strings (char/varchar) or to transparently do so.
done


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.