0 Replies Latest reply: Oct 14, 2013 7:57 AM by Sören Hakola RSS

    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?