?
Solved

ODBC --call failed: [Oracle][ODBC][Ora]ORA-03114: Not connected to ORACLE (#3114)

Posted on 2006-06-26
13
Medium Priority
?
2,759 Views
Last Modified: 2012-05-05
We have an application with an Access 2002 Front end, linked via Oracle's ODBC driver to Oracle 8.1.5 databases running on 2 IBM RS6000's under UNIX.  Much of the coding of the application is done using Oracle triggers.

With the application is an Access "Comprehensive" report that retrieves data from 2 RS6000's, as well as documents stored on Windows 2000 servers and prepares a PDF of every form and document pertaining to the report using Adobe Acrobat 6.0 with Adobe SDK.  At the beginning of its run, using Oracle triggers, the report loads all data from the RS6000 into temporary tables in the microsoft access database.

Too often, when one instance of the report and multiple instances of the application are running, all will terminate with the error "ODBC --call failed: [Oracle][ODBC][Ora]ORA-03114: Not connected to ORACLE (#3114)".

How do I rectify this problem as Comprehensive reports must be run throughout the day?  Are there any settings on the Oracle ODBC driver than can rectify this?   Both the Application and the Comprehensive report are mde files.

sjl
0
Comment
Question by:sjlevine34
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16987745
Guess you need to set the timeout parameter "higher".
Open your query and select the properties, the timeout is by default 60, but I use 600 in general.

Nic;o)
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 16987788
Is there a way to set this globally and for tables?  The application generally writes information to one or several fields in an Oracle table, which then uses Oracle Triggers to complete the remainder of the data pull, either to that or other tables.  Access then pulls the data from the linked tables into its own.

sjl
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16987881
Not that I know of, but are you using "PassThrough" queries ?

Nic;o)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 1

Author Comment

by:sjlevine34
ID: 16987932
I am not using passthrough queries.

I am writing to fields in Oracle tables, and using PL/SQL triggers to retrieve the data from the Oracle database into those tables.   However, in the report, I am using queries to transfer data from the Oracle tables to the local Access temporary tables and those queries have the ODBC timeout property of which you speak.  The application does not use Access queries for its main functions, depending instead on Oracle triggers activated when data written to fields.

sjl
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16987970
Just check the helpfile for the Passthrough queries.
They save time as they are in Oracle SQL written and the query will be executed by the Oracle database engine and inly return the resulting rows. An Access query will first grab all tablerows and then apply the where part, thus putting a lot of unnecessary data over your connection...

Nic;o)
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 16988122
The only problem I now am running into is that I have my search criteria in an access table.  Is there any way to work it into the query without having to create an oracle table for it?  If I do an Oracle table, I have to distinguish records by session ids to allow for multuser use.

sjl
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16988197
You can manipulate the passthrough query by code, as long as you make sure the syntax is OK for Oracle.
Code would look like:

dim qd as DAO.querydef

set qd = currentdb.querydefs("PassThroughQueryName")
qd.SQL = "select a, b, c from tblX where ....."

after the assign statement the query can be used directly.

Nic;o)
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 16992198
Thank you very much.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16994087
Glad I could help, when this is answering your question than you can close the question by accepting the appropriate comment :-)

Nic;o)
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 16994139
I just need to try your solution out first, which I have scheduled to do late this week and early next week.  If it works, I will close it out and award points.  If I run into some issues, I would like to be able to ask further questions.

I apologize for not being able to get to try your solution sooner, but management wants me to complete another project they need in order to meet a timetable involving the whole organization.  But then they definitely want me to try this solution, and I already told them I think it will work.

Again, appreciate your help, tremendously.

sjl
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16994293
No problem, keep me posted :-)

Nic;o)
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 17072673
The passthru printing worked very nicely.

sjl
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17073423
Glad I could help, success with the application !

Nic;o)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question