Empty CLOB in 2.7.0

[NAP-prm]

Hello! After update 2.5.0 to 2.7.0 CLOB fields come empty and not NULL We downgrade to 2.6.0, and CLOB it's OK, but wery slow. How to get more diagnostic data? Tnx

[philflorent]

Hi,

I see in the readme :

_Oracle bugs

This is a list of Oracle bugs that affect or have affected oraclefdw. ... Oracle client 21c is known not to work for CLOB columns (they appear empty). There is no ultimate proof that that is an Oracle bug, but other versions are working fine.

I see in the screen captures that it matches : your instant client is a 21c (innovation release) . I would avoid it to use 19c or 23ai (LTR versions ; better and longer support from Oracle).

Best regards, Phil

[laurenz]

Right, this is a known issue; see #643.

If you have access to Oracle's bug database, I would be happy to known if that is a known bug and if they have a fix.

You'll have to use a different Oracle client version.

[philflorent]

Hi, I had written a message about the possibility to open a service request if you can reproduce the issue on your platforms but Oracle will likely not investigate that. Oracle Client 21c & Oracle Database 11.2.0.3 is NOT a valid combo according to MOS note 207303.1 (it's not even "WAS" in the compatibility matrix it's "NO", it never was supported). In fact the 11.2 server version is not the problem here since issues were also reported with 19c databases, it's the 21c client that fails. But if you still have to access Oracle 11.2.0.3 databases from PostgreSQL the best choice is probably the 19c client anyway. Best regards, Phil

[laurenz]

@philflorent Thanks for your research. Yes, Oracle likes to be secretive about its bugs. If you cannot find anything conclusive, one thing that could be done fairly easily is apply patch sets to a (non-instant) client and see if any of them gets rid of the problem. I'm not suggesting that you should do that, but someone who is suffering from the problem and has access to their patches could feel motivated.

[mkgrgis]

@laurenz, what about ereport in case of Oracle client 21.x + CLOB? This is not first discussion about the problem. Maybe, we can store oracleClientVersion in FDW structures and fast compare it during column processing?

[darold]

FYI, with Ora2Pg I use the following workaround when retrieving data :

       if option empty_lob_null is true then
                 target_col = "CASE WHEN dbms_lob.getlength(colname) = 0 THEN NULL ELSE colname END,";
       end

So as you know the data type of the column in oracle_fdw you could do the same in the pushdown query.

[NAP-prm]

2.7.0 + 19.24 + 11.2.0.3 = Ok. Thx!

[laurenz]

@mkgrgis Good idea; done in 26a7636762c3337a36e9e3398d7d7b442a46056a.

@darold Are we talking about the same problem? This is not about LOBs that are actually empty, but about CLOBs with content that appear empty when using Oracle 21.

[darold]

@laurenz sorry I have read that "CLOB fields come empty and not NULL" in first note and though that it could be solved like in Ora2Pg. On my side I have no report about such issue with Perl DBD::Oracle but I have not tried yet. Sorry for the noise.

[laurenz]

@darold No problem, thanks for the clarification.