"Martin Evans" <[EMAIL PROTECTED]>, "dbi-users" <[email protected]> unsubscibi me plz - unsubsicrib mi please <[EMAIL PROTECTED]> how to
> -----Ursprüngliche Nachricht----- > Von: "John Scoles" <[EMAIL PROTECTED]> > Gesendet: 18.07.08 19:16:35 > An: Martin Evans <[EMAIL PROTECTED]> > CC: dbi-users <[email protected]> > Betreff: Re: Problem with DBD::Oracle ora_auto_lob not working > Hi Martin I found the bug (well actually an omission) and fixed it so > now It will return the data you expect. > > I committed the changes to Trunk > http://svn.perl.org/modules/dbd-oracle/trunk so if you will try the code > from there and see if it works for you now. > > Seem the not all the attributes for the SP were not being passed along > so in actual fact it was only need the addition of 6 lines of code. As > is normal with anything to do with DBD::Oracle (well actully C in > general) it took 2 hours to find out where to put those lines fro the 2 > min change. > > cheers > John Scoles > > Martin Evans wrote: > > John Scoles wrote: > >> Ouch nasty one Martin. > >> > >> But I think that DBD oracle is doing the correct thing here. > > > > Well that is what I'm after clarification on. The pod for ora_auto_lob > > says "retrieves the contents of the CLOB or BLOB column in most > > circumstances". What are the circumstances it does /not/ retrieve the > > clob/blob data and returns a lob locator instead? > > > >> I think when you bury the 'SELECT x from martin; ' in the SP like > >> this you are essentially only ever going to get a lob locater back as > >> that is what you are asking for. > > > > I don't understand your comment here. I never asked for a lob locator, > > I asked for the contents of column 'x' and if I run this select in > > Perl I get the clob contents back (presumably because ora_auto_lob is > > on by default and works on normal statement handles). It is only when > > I run the select in an oracle procedure and return the cursor which > > gets magicked into a DBI statement handle and then run a fetch on that > > statement that I get a lob locator. > > > >> Of couser you could use all the neat lob function in DBD Oracle on it > >> like 'ora_lob_read', 'ora_lob_write' etc, which of course kind of > >> defeats the purpose of this exercise. > > > > Actually, I do not mind running ora_lob_read to get my data back from > > the lob locator (although I'd prefer ora_auto_lob did it for me) but > > my data is utf8 and the pod says: > > > > "Warning: Currently multi-byte character set issues have not been > > fully worked out. So these methods may not do what you expect if > > either the Perl data is utf8 or the CLOB is a multi-byte character set > > (including uft8). The current behaviour in these situations may not be > > correct and is subject to change." > > > > which suggested to me that you can't get utf8 data back from > > ora_lob_read. What I am actually trying to do is hide the SQL and any > > schema from the perl by placing it in the database in a package, I'm > > not worried how I get the data out when the procedure is called. > > > >> If you are using DBD:ORacle 1.21 you would try using the > >> ora_type=>SQLT_CHR or ora_pers_lob. > > > > I did try using ora_pers_lob but it made no difference. > > > >> I going to release a new version of the code next week 1.22 that have > >> a number of LOB improvements that might help. > > > > Excellent, I look forward to that. > > > >> I will see if I can recreate you problem today right not I am little > >> busy with real work. > > > > Thanks. I have added to my test case a little to actually write utf8 > > data and get it back - it would seem that the pod warning with respect > > to utf8 data not coming back from ora_lob_read /may/ be out of date. > > See new code example below. > > > > use DBI (data_diff); > > use warnings; > > use strict; > > use Data::Dumper; > > use DBD::Oracle qw(:ora_types); > > use Encode; > > use charnames ':full'; > > > > my $h = DBI->connect('dbi:Oracle:XE', 'xxx', 'xxx'); > > > > eval {$h->do(q/drop table martin/);}; > > $h->do(q/create table martin (x clob)/); > > my $data = "\x{263a}xxx" . chr(0x05d0) . "\N{ARABIC LETTER ALEF}"; > > print "Length of input data: " . length($data) . "\n"; > > $h->do(q/insert into martin values (?)/, undef, $data); > > > > my $createproc = << 'EOT'; > > CREATE OR REPLACE PROCEDURE p_martin(pc OUT SYS_REFCURSOR) AS > > l_cursor SYS_REFCURSOR; > > BEGIN > > OPEN l_cursor FOR > > SELECT x from martin; > > pc := l_cursor; > > END; > > EOT > > > > $h->do($createproc); > > > > my $s = $h->prepare( > > q/begin p_martin(?); end;/, {ora_auto_lob => 1}); > > my $sth; > > $s->bind_param_inout(1, \$sth, 50000, > > {ora_type => ORA_RSET}); > > $s->execute; > > my ($lobl) = $sth->fetchrow; > > print Dumper($lobl); > > my $length = $h->ora_lob_length($lobl); > > print "lob length: $length\n"; > > > > my $cdata = $h->ora_lob_read($lobl, 1, $length); > > print data_diff($data, $cdata); > > > > which prints: > > > > Length of input data: 6 > > $VAR1 = bless( do{\(my $o = 142150356)}, 'OCILobLocatorPtr' ); > > lob length: 6 > > > > and does not print anything for data_diff suggesting to me the input > > and output data are the same. > > > > Martin > _____________________________________________________________________ Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! http://smartsurfer.web.de/?mc=100071&distributionid=000000000066
