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

Mon, 14 Oct 2013 11:33:35 GMT

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?

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

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