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?!
