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?
