Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
For the first time, I'm dealing with the combination of QlikView and an Oracle database. The version number of the Oracle database is: 10.2.0.4.0, 64 bit.
My question is: Is there a maximum record width in either QlikView or OleDB driver, when reading Oracle databases? If so, where is the limit?
Some information:
I'm trying to execute a select command on a table that has following structure (only length give here):
Oracle datatype | column given in select command | cumulative length sum |
VARCHAR2 (14) | X | 14 |
VARCHAR2 (3) | X | 17 |
VARCHAR2 (3) | X | 20 |
VARCHAR2 (4) | ||
VARCHAR2 (5) | X | 25 |
VARCHAR2 (4) | ||
VARCHAR2 (50) | X | 75 |
VARCHAR2 (2) | X | 77 |
VARCHAR2 (1) | X | 78 |
DATE | X (8 chars ?) | 86 |
VARCHAR2 (4) | X | 90 |
VARCHAR2 (5) | X | 95 |
VARCHAR2 (2) | X | 97 |
VARCHAR2 (4) | X | 101 |
VARCHAR2 (10) | ||
VARCHAR2 (13) | ||
VARCHAR2 (35) | X | 136 |
NUMBER (3,1) | X (3+1 ?) | 140 |
NUMBER (3,1) | X (3+1 ?) | 144 |
VARCHAR2 (1) | X | 145 |
NUMBER (5,3) | X (5+3 ?) | 153 |
NUMBER (4,2) | ||
DATE | X (8 chars ?) | 161 |
VARCHAR2 (2000) | substr(<field>,0,1811) | 1972 |
VARCHAR2 (4) | ||
DATE | ||
VARCHAR2 (30) | X | 2002 |
DATE | ||
VARCHAR2 (12) | ||
VARCHAR2 (12) | ||
VARCHAR2 (100) | ||
VARCHAR2 (200) | ||
DATE | ||
VARCHAR2 (1) |
I.e. Sum length = maximum 2002 chars ?? Since I'm not sure how number and date fields are handled, I'm really not sure about the maximum width of the row! I have not seen this restriction when selecting data in SQL Server databases.
I have to do the substr() function in the SQL select <fields,...> from <schema>.<table> part of the query, otherwise QlikView crashes. I.e if I do right(<field>,1811) in the QV part of the load statement, the script crashes QlikView.
If I increase the number +1 to substr(<field>,0,1812) it crashes.
If I comment out one field and increases the parameter in substr() function the same amount as the field just "gave away", the reload succeeds.
Conclusion:
There is some kind of maximum width, but I cannot figure out the value based on these small tests.
Drivers:
OleDB: Oracle's own driver, installed by Oracle Client - installation. Version: 11.02.00.03, 64 bit.
In the connection string in QlikView, I've added the following parameters:
FetchSize=1000000;CacheType=Memory;
(Another table to be read contains over 90 miljon rows, so I had to tune up some parameters to get more efficiency out of the load.)
NLS_LANG is set in registry to: FINNISH_FINLAND.WE8MSWIN1252
NB! There is no Excel on the server to test with either, unfortunately...
Does anyone have any input on this?
Greetings,
Sören
Edit:
I temporarily swapped to ODBC just to check load speeds and benchmarking and tested just for fun to remove the substring() function. And the reload completed wihtou problems?!?!!
I noticed that the ODBC driver' Fetch Buffer Size was configured to 128000. I changed the value in the OleDB connection string from 1000000 to 128000 and VOILÀ! The reload completed successfully without the substr() function.
Conclusion:
FetchSize with value 1000000 was somehow too big?!
Anyone had some similar experience?