Qliksense does not fetch all records from Oracle View

[Asha]

Hi Everyone,

I'm trying to load a View from Oracle which has 800,000+ records. But Qlik fetches only 31,000 records. It works fine with other tables and Views.

My SQL is as simple as below

Select * from TableName ;

So far I have tried below

1. Deleting my connection and recreating it but it did not help.

2. Tried the solution from here.

Thanks,

Asha

[sunny_talwar]

Are you hitting the correct data source? May be Prod vs dev issue?

[Asha]

Yes I'm in the right environment. I've recreated my connection to make sure 🙂

[sunny_talwar]

Have you tried with the alternate connection type? If you are using ODBC, try OLEDB... or vice versa

[tcullinane]

I've had issues with Oracle not returning all records before, although it was random, it appeared to be an Oracle issue with cached queries.

try
sql alter session set "_nlj_batching_misses_enabled" = 0;
after the odbc connect statement

It forces the query to run on the data not on memory, possibly slower and more demanding on the oracle db

edit: you may need additional rights from the Oracle admin

[Asha]

Hi Tcullinane,
Thanks for the response!
I just tried this adding "_nlj_batching_misses_enabled" = 0; after my connect statement, it connected and ran but it did not fetch all records.

[tcullinane]

If you know a key field that should always be populated run an
Load *; SQL SELECT count(KEY_FIELD) as Count_Key FROM DB.VIEW;
to verify you have access to the data you expect

If you dont it may be an issue with oracle security for the user you are connecting with

[Asha]

I'm Currently using default Oracle Connection.
I can only see 32 Bit version of 'Microsoft OLE DB Provider for Oracle' but qlik is 64 bit.
Do you know how can I connect through OLE DB 64 Bit Version?

Thanks,
Asha

[Asha]

Yes I've tried that earlier, there is no security applied.

[Asha · Accepted solution]

We have raised a ticket with Qlik.

They were able to solve it by adding  Parameters under 'Advanced' in the connection details as below  and advised to use OLE DB connection over ODBC.

Thanks,

Asha