Need some Oracle expert to explain :)

[aehimself]

Hello,

I've been contacted by one of my colleagues that the database manager I built with Zeos do not show the same result as PL/SQL.
The query is a simple SELECT, but against a view, not a table. Database and schema is the same.

With PL/SQL, SELECT * FROM myview returns some 25k-ish records, my database manager returns 1.
As an interesting fact, if I execute SELECT COUNT(1) FROM myview, my database manager returns the same, 25k-ish result.

I also realized is there is a warning logged by Zeos when doing the select:
[2021.10.26 12:52:07] database: Fetch FETCH ROW ORA-24347: Warning of a NULL column in an aggregate function (#24347)
https://support.oracle.com/knowledge/Or ... 361_1.html

Will this be something with Zeos not querying the results (or handling the above warning) properly, or some deep access right magic of Oracle...?

[aehimself]

I contacted our DB expert to try to figure out what is causing the issue and to make a test case for me.

In the mean time, if I write SELECT * FROM myview WHERE ROWNUM < 1000000 all records are being downloaded and displayed, without Oracle throwing the warning...?!

[aehimself]

Turns out the issue can be fixed on Zeos's side, see the pull request on GitHub. Unfortunately I can not tell which change fixed it but according to https://github.com/oracle/node-oracledb/issues/80 the same issue was fixed by modifying the same check in a different software.

I hope it doesn't break too many things :)

[marsupilami]

Hello AEhimself,

I applied your patch. Do we need to check if this also needs to be fixed in Zeos 7.2? Is it easily possible to create a test case that we can use to test for this problem?

Best regards,

Jan

[aehimself]

Jan,

As far as I can see yes, 7.2 is also affected. Fortunately the method TZOracleResultSet.Next is almost the same, you can try to apply the same patch to that branch too.

As for the test case yes, I'm still waiting our expert to do his magic but he said he won't be able to look at this until next week for sure. Once I have any updates I'll post it.

I saw a couple of tests failing after applying my changes - am I safe to assume it was a network connection thing and not my code?