Re: DBD::Oracle 11gr2 & ORA-38909

Ok I guess it is back to square 1 on this.

Unfortunetly no time to look at it today

Will write up a small test script and see if my patch actually does anything

You may hear from me in a few days

Cheers
John


Scott T. Hildreth wrote:
> On Fri, 2010-05-21 at 09:23 +0100, Martin Evans wrote:
>   
>> John Scoles wrote:
>>     
>>> Ok I have patched up a solution I think will work across the board and you
>>> can find it here
>>>
>>> http://svn.perl.org/modules/dbd-oracle/branches/oci_batch
>>>
>>> here are the details
>>>
>>> ora_oci_batch
>>>
>>> For 11g users you may encounter an error while using the execute_array in
>>> that it does not
>>> return a full list of tuples.  This seems to be a result in that a statement
>>> can only
>>> have 'LOG ERRORS' or 'SAVE EXCEPTIONS'set, By setting this flag to a value
>>> should stop this
>>> problem error.
>>>
>>> For convenience I have added support for a 'ORA_DBD_OCI_BATCH'
>>> environment variable that you can use at the OS level to set this
>>> value. It can also be set as an attribute on both the Connect and Prepare.
>>>
>>> Unfortunately I can't test it (do not have an 11g box yet)  so It will stay
>>> in the above branch until it is tested hopefully by you Scott
>>>
>>> Cheers
>>> John Scoles
>>>
>>> --
>>> See Pythian's Alex Gorbachev, co-author of "Expert Oracle Practices" at NoCOUG Spring Conference May 20th.
>>> Details, interview & book chapter in the May NoCOUG Journal: http://bit.ly/alexnocoug
>>>
>>>       
>> I'm not sure why I seem to have ignored your mail but I just noticed it
>> again - sorry for the delay.
>>
>> I checked out the branch you mentioned and
>>
>> export ORA_DBD_OCI_BATCH=1
>>
>> but 26exe_array still seems to fail for me:
>>     
>
> Sorry John, I meant to test earlier but its been a busy week.
>
> Fails for me as well.
>
>   DB<7> x  $dbh->{'ora_oci_batch'}
> 0  1
>   DB<8> n
> main::(../tst_exec_ary.pl:13):  my $sth = $dbh->prepare("Insert into TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY");
>   DB<8> n
> main::(../tst_exec_ary.pl:15):  $sth->bind_param_array(1, [ qw(One Uno Il oNe) ]);
>   DB<8> n
> main::(../tst_exec_ary.pl:16):  $sth->bind_param_array(2, [ 2, 22, 0, 222 ]);
>   DB<8> n
> main::(../tst_exec_ary.pl:17):  $sth->bind_param_array(3, [ qw(20070101 20080101 20090101 20060101) ]);
>   DB<8> n
> main::(../tst_exec_ary.pl:21):  $sth->execute_array({});
>   DB<8> n
> DBD::Oracle::st execute_array failed: ORA-38909: DML Error logging is not supported with BATCH ERROR mode (DBD ERROR: OCIStmtExecute) [for Statement "Insert into TestArray Values(?, ?, ?) LOG ERRORS INTO ERR_TESTARRAY"] at ../tst_exec_ary.pl line 21.
>  at ../tst_exec_ary.pl line 21
>
>   
>> martin@bragi:~/svn/dbd-oracle/branches/oci_batch$ prove -vb t/26exe_array.t
>> t/26exe_array.t ..
>> 1..17
>> ok 1 - use DBI;
>> ok 2 - The object isa DBI::db
>> ok 3 - ... execute_array should return true
>> ok 4 - ... we should have 10 tuple_status
>> ok 5 - ... execute_array should return false
>> ok 6 - ... we should have 10 tuple_status
>> ok 7 - ... we should get text
>> ok 8 - ... we should get -1
>> ok 9 - ... we should get a warning
>> ok 10 - ... execute_for_fetch should return true
>> not ok 11 - ... we should have 19 tuple_status
>>
>> #   Failed test '... we should have 19 tuple_status'
>> #   at t/26exe_array.t line 128.
>> #          got: 10
>> #     expected: 19
>> ok 12 - ... execute_array should return flase
>> ok 13 - ... we should have 10 tuple_status
>> not ok 14 - ... we should have 48 rows
>>
>> #   Failed test '... we should have 48 rows'
>> #   at t/26exe_array.t line 154.
>> #          got: 30
>> #     expected: 48
>> ok 15 - ... execute_array should return true
>> ok 16 - ... \#5 should be a warning
>> ok 17 - ... we should have 10 tuple_status
>> # Looks like you failed 2 tests of 17.
>> Dubious, test returned 2 (wstat 512, 0x200)
>> Failed 2/17 subtests
>>
>> Test Summary Report
>> -------------------
>> t/26exe_array.t (Wstat: 512 Tests: 17 Failed: 2)
>>   Failed tests:  11, 14
>>   Non-zero exit status: 2
>> Files=1, Tests=17,  0 wallclock secs ( 0.02 usr  0.01 sys +  0.05 cusr
>> 0.01 csys =  0.09 CPU)
>> Result: FAIL
>>
>> This was using oracle 11.1 server and 11.1 instant client.
>>
>> If I've not set the right thing let me know.
>>
>> Martin
>>     
>
>