UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Function Sequence Error on Oracle Pass-Through Query    
 
   
kforward
post Mar 16 2009, 12:00 PM
Post#1



Posts: 268
Joined: 13-February 08
From: Nashville, TN, USA


Hello UAers,
I've scoured the internet, and can't seem to find solution for this error. I am running an Access append query to a local table that gets its data from a pass-through query to Oracle 9i. I can get it to work on smaller datasets (when date range is only a day or two - returns approx. 1 million records successfully into my local table), but I need it to include an entire month's worth of data (approx 12 million records - note: I've had no trouble with that size recordset before when using a regular Access append query on same dataset, my .mdb stays under 1gb w/no issues - but the pass through query is much faster and far more preferable than lettnig Access Jet crunch this one - the pass through is about 5x faster!). I am using Micrsoft ODBC for Oracle driver.
The exact error is: '[Microsoft][ODBC Driver Manager] Function sequence error (#0)
THere is the code I'm passing through, which works w/o error as previously mentioned on smaller recordsets:
CODE
SELECT  /*+ FIRST_ROWS */
T.AMOUNT,
T.ATTEMPT_COUNT,
T.CUSTOMER_ID,
T.SOURCE_TRANS_TYPE,
T.STATE,
T.TRANSACTION_DATE,
T.TRANSACTION_ID,
T.SERVICE,
A.CLOSED_REASON,
A.FINANCIAL_TRANS_TYPE,
A.SEQUENCE_NUMBER,
A.CURRENT_ATTEMPT_STATE_NAME,
ATS.ACTUAL_DATE,
ATS.NAME,
ATS.PLAN_DATE,
F.COMPANY
FROM TRANSACTION T, ATTEMPT A, ATTEMPT_STATE ATS, FORMATTER_DATA F
WHERE T.TRANSACTION_DATE>to_date('01/30/09','mm/dd/yy')
AND T.TRANSACTION_DATE<to_date('02/28/09','mm/dd/yy')
AND T.TRANSACTION_OID = A.TRANSACTION_OID
AND A.ATTEMPT_OID = ATS.ATTEMPT_OID
AND (ATS.NAME= 'P' OR ATS.NAME='A')
AND T.FORMATTER_DATA_OID=F.FORMATTER_DATA_OID
Go to the top of the page
 
MicroE
post Mar 17 2009, 08:17 AM
Post#2


UtterAccess VIP
Posts: 4,034
Joined: 22-February 05
From: NY - USA


I receive this error quite often when using ODBC with Oracle. I am unable to determine the exact cause – but from what I gather – this happens when the Oracle database is returning the records too slowly for MS Access to process.
For your situation I would suggest:
Instead of append from a pass through query – create a make table query using the pass-trough query as the source.
Name the Make-Table something like “Temp_TRANSACTION”
Then create an append query that appends the records from the local Temp_TRANSACTION table to your destination table.
This process will execute faster in MS Access because a Make-Table query does not have to check destination table validation (like an append query does). Now your append query is working from a local MS Access table rather than a Pass-Trough.
Go to the top of the page
 
kforward
post Mar 17 2009, 11:10 AM
Post#3



Posts: 268
Joined: 13-February 08
From: Nashville, TN, USA


Hi Matt,
That did the trick! I work with Oracle a lot as well so this will come in very handy in the future.
Thanks again,
Kent
Go to the top of the page
 
MicroE
post Mar 17 2009, 11:36 AM
Post#4


UtterAccess VIP
Posts: 4,034
Joined: 22-February 05
From: NY - USA


Glad I could help.
Go to the top of the page
 
Whyteboar
post Mar 25 2009, 12:04 PM
Post#5



Posts: 7
Joined: 1-November 07



I had run into this same error message on a pass-through query that was already a make table type. Very spotty. If I made a change to the fields selected it would run once, but not a second time. What seemed to work best was upping the Timout setting a bit. Oddly enough, that helped. At least so far.
raig
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 08:44 PM