Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Is there a maximum record width (Oracle 10g) ?

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)X14
VARCHAR2 (3)X17
VARCHAR2 (3)X20
VARCHAR2 (4)
VARCHAR2 (5)X25
VARCHAR2 (4)
VARCHAR2 (50)X75
VARCHAR2 (2)X77
VARCHAR2 (1)X78
DATEX (8 chars ?)86
VARCHAR2 (4)X90
VARCHAR2 (5)X95
VARCHAR2 (2)X97
VARCHAR2 (4)X101
VARCHAR2 (10)
VARCHAR2 (13)
VARCHAR2 (35)X136
NUMBER (3,1)X (3+1 ?)140
NUMBER (3,1)X (3+1 ?)144
VARCHAR2 (1)X145
NUMBER (5,3)X (5+3 ?)153
NUMBER (4,2)
DATEX (8 chars ?)161
VARCHAR2 (2000)substr(<field>,0,1811)1972
VARCHAR2 (4)
DATE
VARCHAR2 (30)X2002
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?

Community Browser