John wrote: I works without the ORA_CLOB because it is just a straight insert up to a set value ,the value of 'LongReadLen' me thinks?
Yes, that's correct we have ours set to about 500k It looks like the solution John suggested worked. By taking out the ORA_CLOB attributes and issuing a GRANT "SELECT_CATALOG_ROLE" TO <user>; we are now inserting editing and reading CLOBS correctly. Thanks everyone for the help! Carl Furst CMS Developer MLB Advanced Media -----Original Message----- From: John Scoles [mailto:sco...@pythian.com] Sent: Tuesday, December 21, 2010 11:13 AM To: dbi-users@perl.org Subject: Re: DBD::Oracle, 10g Lob Refetch problem On 21/12/2010 10:48 AM, Furst, Carl wrote: > Thanks to all of you for taking the time to answer my questions. > Unfortunately I don't have the output from the compile. However consider > that we are able to connect, select, insert, and update regular fields, it > would seem that for the most part things were successful, unless I'm missing > something. > > As to the make test. Pretty much all tests passed, however there was one > test that failed and that was, you guessed it, the 31lob.t test. The > 31lob_extended.t passed. > > So 31lob.t produced : > t/31lob.t ............... 2/12 DBD::Oracle::db do failed: ORA-00942: table > or view does not exist (DBD ERROR: error possibly near<*> indicator at char > 14 in 'select * from<*>v$session where 0=1') [for Statement "select * from > v$session where 0=1"] at t/31lob.t line 79. If the above is the case then I think I know what your problems int In later release of 10 and 11 they have removes some permissions by default. One of them is the create session Check with your DBA that the use that is connection has the create session privilege. Usually this get turned off with an upgrade to 10 or 11. The root cause is DBD::Oracle will want to create a new session for your ORA_CLOB to use to insert all of your clob no matter how big. I works without the ORA_CLOB because it is just a straight insert up to a set value ,the value of 'LongReadLen' me thinks? Hope this helps. Cheers John Scoles > the user doesn't have access to v$session and we can't really change that. > > Anyway, as I have updated before it would seem that the use of the ora_types > constants is what's affecting this. When I bind parameters using an %attr > hash with bind params, it triggers the lob refetch issue. Without it, > however things seems fine. > > my bind params call was something like > > $sth->bind_param($binder_symbol, $value, {ora_types=>ORA_CLOB, > ora_field=>$fieldname}); > > So without the anonymous hash ref, things seem hunky dory, however the docs > say I need those params for handling lobs, so it has me worried. > > I'll keep y'all posted. > > Thanks, > > Carl Furst > CMS Developer > MLB Advanced Media > -----Original Message----- > From: John Scoles [mailto:sco...@pythian.com] > Sent: Tuesday, December 21, 2010 8:51 AM > To: dbi-users@perl.org > Subject: Re: DBD::Oracle, 10g Lob Refetch problem > > On 20/12/2010 3:17 PM, Furst, Carl wrote: >> Hello, >> >> I just built DBD::Oracle 1.26 on Solaris SPARC 2.10 using perl 5.8.5 32 > bit >> against client 10.0.2.4 >> >> We've been having trouble since day one. The biggest problem is that we > are >> having a problem writing LOB fields. We get the following error: >> >> DBD::Oracle::st execute failed: ORA-00903: invalid table name (DBD >> ERROR: OCIStmtExecute/LOB refetch) >> > To start we will need to see the output from the perl Makefile.PL and > the make, and make test to see if there is something wrong with the way > it is being built > >> We think it's the LOB refetch that's causing the issue. >> >> the encoding of the database and the NLS_LANG parameter are both UTF8 >> nls_lang specifically is AMERICAN_AMERICA.UTF8 >> >> If anyone has any advice about this, it would be a big help. >> > Next we will need to see an example of your code as there are at least 4 > different ways to write lob fields with DBD::Oracle > >> My questions are the following: >> 1) do we need an actual Oracle server to build the DBD - if so what libs >> would we need to link against? > No you do not need a server you only need the client. Instant client > should work fine >> 2) Has anyone else experienced this; building again lib32 client libs. > Solaris SPARC tends to have more problems that other OS when attempting to > compile DBD::Oracle in 32bit but since you > have it working you should be past this point > > >> 3) What role does oraperl have in all this? If oraperl fails to compile, > is >> that a blocker for DBD? >> > No it is a separate and deprecated hunk of code for perl 4. It may be a > symptom of a deeper problem so we need to see the Makefile.pl and make > output to know for sure. > > Cheers > John Scoles >> Thanks in advance, >> >> Carl Furst >> CMS Developer >> MLB Advanced Media >> >> >> >> >> >> >> >> ********************************************************** >> >> MLB.com: Where Baseball is Always On > > > > > > > > ********************************************************** > > MLB.com: Where Baseball is Always On
smime.p7s
Description: S/MIME cryptographic signature
********************************************************** MLB.com: Where Baseball is Always On
