topic Qliksense does not fetch all records from Oracle View in App Development

Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 15:17:03 GMT

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

Re: Qliksense does not fetch all records from Oracle View

sunny_talwar — Fri, 08 Feb 2019 15:26:34 GMT

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

Re: Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 15:28:45 GMT

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

Re: Qliksense does not fetch all records from Oracle View

sunny_talwar — Fri, 08 Feb 2019 15:32:11 GMT

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

Re: Qliksense does not fetch all records from Oracle View

tcullinane — Fri, 08 Feb 2019 15:40:38 GMT

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

Re: Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 15:49:51 GMT

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.

Re: Qliksense does not fetch all records from Oracle View

tcullinane — Fri, 08 Feb 2019 16:01:31 GMT

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

Re: Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 16:02:02 GMT

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

Re: Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 16:03:33 GMT

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

Re: Qliksense does not fetch all records from Oracle View

Asha — Fri, 08 Feb 2019 19:54:08 GMT

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

Re: Qliksense does not fetch all records from Oracle View

francesco_menin — Tue, 24 Sep 2019 15:39:13 GMT

Hi Asha,

did you by chance encounter this issue with QlikView as well? I've had this happening with the original Oracle ODBC drivers.