Fetch out of Sequence / Insufficient Privileges on Underly

building semantic layer
1

ects of View

Oracle 7.3.3 + BO4.1.2

A while back there was a thread on a ‘Fetch out of Sequence’ error while reports were being generated in BusObj but where the error wasn’t occuring in SQL*Plus. I have had a look back at the mails on the archive web site (which is excellent so more of us should use it!) but I want to take this one step further.

I have a fairly simple query (4 objects + 1 condition) which depending on the condition value only returns around 30 rows. The definition of the objects are straight ‘select froms’ with the underlying columns having the following definitions

Object 1 = Table1.Column1 Varchar2(8)
Object 2 = Table1.Column2 vc2(1900)
Object 3 = Table 1.Column3 vc2(90)
Object 4 = Table 2.Column4 vc2(60)

The condition is on Column4 and the join is from Table1.Col1 to Table2.Col2

The average length of the data in column2 is nearer 200chrs than the max 1900

Anyway, the SQL that BusObj generates runs fine for the majority of the values for the condition column both in BusObj and SQLPlus. On some values however, I get the ‘Fetch out of Sequence’ error message in BusObj but it runs fine in SQLPlus. Now, for those who a) can’t remember the original thread or b) can’t be bothered to look at the archives, one main cause of this behaviour is the Fetch size setting in BusObj being different to the equivalent Arraysize setting in SQLPlus. Changing the SQLPlus setting to be the same (ie 50) results in the error “buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.” error. So far so good (ie can get errors in both environments!)

Now, changing the fetchsize in BusObj to the same as the arraysize in SQL*Plus where the query works (ie 15) does not stop the error.

Changing connections from asynchronous to synchronous and keeping connections open etc makes no difference.

Can anyone suggest some changes that can be made or any know resolution to this problem?

p.s The reason for the 2 error messages in the subject of this mail is that the ‘Fetch out of Sequence’ is the error message I get when running the SQL as freehand SQL through a personal connection and the ‘Insufficient Privileges on Underlying Objects of View’ error is the one I get running through a secured connection.

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)

2

Obj ects of View

Cirkel, Jonathan D schrieb:

…
p.s The reason for the 2 error messages in the subject of this mail is that the ‘Fetch out of Sequence’ is the error message I get when running the SQL as freehand SQL through a personal connection and the ‘Insufficient Privileges on Underlying Objects of View’ error is the one I get running through a secured connection.

  1. Did you also try to set the array size on BusObj to 1 ?

  2. A similar problem at one of our customers: They had problems with network routers truncating packets from SQLNet (actually this was a known SQLNet - bug, occures(ed) if IP packet sizes in different router segments do not match…)

  3. Did you run the query directly from SQL*Net over the network? Error ‘Insufficient Privileges on Underlying Objects of View’ lets me assume there are select-rigtht problems in the database.

Hope this helps…
Walter

–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)

3

Obj ects of View

Walter,

  1. Did you also try to set the array size on BusObj to 1 ?

Interestingly, I went down to a setting of 10 but no lower. I have now worked up from a setting of 1 to 8 and the query now works without an error. From 9 upwards it fails. Thanks, hopefully this will do as a work round at the moment although having such a low setting might well impact the performance of some reports.

  1. A similar problem at one of our customers: They had problems
    with network
    routers truncating packets from SQLNet (actually this was a
    known SQL    Net - bug,
    occures(ed) if IP packet sizes in different router segments do
    not match…)

I haven’t tested this (and I’m not sure whether I know how!)

  1. Did you run the query directly from SQL*Net over the network? Error
    ‘Insufficient Privileges on Underlying Objects of View’ lets me assume there are
    select-rigtht problems in the database.

This is the first thing I do with any SQL errors in BusObj. The ‘Insufficient Privs on Underlying objects of View’ seems to be a fairly standard error message in BusObj at the moment and rarely has anything to do with the actual problem. As in this case, the real error is only shown either in SQL*Plus or running across a ‘Personal’ connection in BusObj. Why I get different error messages by running the same SQL through two different types of BusObj connection (pointing to exactly the same Oracle accounts and with the same settings) I really don’t know.

Thanks for your input/solution.

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Hope this helps…
Walter

–
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
For old 3.1 info:
Penn: Page not found
Search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)