Problem with CLOB, queried with nvl() under Oracle 12C R2

[s_grosskr]

Dear ODAC-Support Team,
I have a problem with customers which upgraded their installation to Oracle 12 R 2.
When you query a lot of lines from a table with an empty CLOB Column and you query that column with nvl(mycolumn, 'my default value'), then approx. at rownum 100 the column is empty instead of containing 'my default value').
As I use this construct in a plenty of scripts, this is really a very ugly bug.
I'm using ODAC 9 in the latest Version 9.7.28, on the client side I tested with Oracle Client 11 and 12.
I'm still using Delphi XE2 with Update 4.

You can prepare a test scenario with the following script:

Code: Select all

CREATE TABLE TEST_CLOB_ORA12
(
  ID                               VARCHAR2  (32)      DEFAULT sys_guid() NOT NULL
, BEM                              VARCHAR2  (4000)
, BEM_CLOB                         CLOB
, BEM2_CLOB                        CLOB
);

ALTER TABLE TEST_CLOB_ORA12
ADD CONSTRAINT PK_TEST_CLOB_ORA12 PRIMARY KEY ( ID );

DECLARE
  vLfdNr integer;
begin
  FOR vLfdNr IN 1..200
  LOOP
    insert into TEST_CLOB_ORA12 (bem, BEM2_CLOB) values ( 'Row Nr ' || vLfdNr, 'Bem for Row Nr ' || vLfdNr );
  END LOOP;
end;
/

COMMIT;

and test it with this query:

Code: Select all

select id
     , bem
     , NVL( bem_clob, 'nvl_value') as test_field
     , BEM2_CLOB
from TEST_CLOB_ORA12

When you query the table with a TOraQuery or TSmartQuery, approx. at Reord 100 the field "test_field" becomes empty.
With Oracle 12C R1 everything went ok.

I have prepared a little Demoproject, based on DUnitX, which I can send you, if necessary.
Could you please confirm this bug and look for a solution for this problem?

Thanx in advance for your help.
With regards,
Stefan "ostijo" Grosskreutz

[MaximG]

Thank you for providing the description. Please send the composed test sample via the e-support form https://www.devart.com/company/contactform.html

[s_grosskr]

Just for the records:
The problem does not show up with version 10.2 / 10.3 of ODAC.
I suppose it was fixed in version 10.2, as in the version histoy it reads

Bug with reading LOB data in Oracle 12 is fixed.

see: https://www.devart.com/odac/revision_history.html

Maybe the problem was similar to the problem described in thread viewtopic.php?t=36942 ("bug with CLOB in Oracle 12.2").

So the case can be considered as closed!
Thank you!
With regards,
Stefan "ostijo" Grosskreutz

[MaximG]

We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our product usage.