Question

Oracle 9i reading an oracle 10g table and reciving End-of-file on communication channel error

Asked by: Mike_Brinton

Guidance please.  I am receiving an ORA-03113: end-of-file on communication channel when I run the first query listed below.  When I change the reference to 'SYSDATE' in the WHERE clause to a hard coded date, as in the second query, and the query executes correctly.  I am using 'SYSDATE' to build columns in both queries, and I am not having a problem when the WHERE clause has the hard coded date.  Also, I do not have a problem with the first query executing when I remove the condition in the warehouse clause with 'SYSDATE'.
Can someone please provide some suggestions at to what is causing the problem?

SQL 1:
SELECT distinct
       p.policy_number phx_policy,
       POL_NUM         whs_policy,
       PS_ACCOUNTING_DATE,
       length(ps_accounting_date) date_len,
       to_char(add_months(SYSDATE,-1),'yyyymm') calcualted_acc_date,
       length(to_char(add_months(SYSDATE,-1),'yyyymm')) date_len_2
  FROM MV_DON_HSB@CLOPHSB,     --this table is in an oracle 10.2 unix database
       policy p                --this table is in an oracle  9.2 windows database
    WHERE length(trim(p.policy_number)) = 10
   and PS_ACCOUNTING_DATE = to_char(add_months(sysdate,-1),'yyyymm')
   and pol_num = p.policy_number
 
SQL 2:
SELECT distinct
       p.policy_number phx_policy,
       POL_NUM         whs_policy,
       PS_ACCOUNTING_DATE,
       length(ps_accounting_date) date_len,
       to_char(add_months(SYSDATE,-1),'yyyymm') calcualted_acc_date,
       length(to_char(add_months(SYSDATE,-1),'yyyymm')) date_len_2
  FROM MV_DON_HSB@CLOPHSB,     --this table is in an oracle 10.2 unix database
       policy p                --this table is in an oracle  9.2 windows database
    WHERE length(trim(p.policy_number)) = 10
   and PS_ACCOUNTING_DATE = to_char(add_months('01-SEP-2009',-1),'yyyymm')
   and pol_num = p.policy_number
                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-09-22 at 12:03:33ID24752684
Tags

oracle 9i

,

oracle 10g

,

sysdate

,

ora-03113

,

end-of-file on communication channel

Topics

Oracle 9.x

,

Oracle 10.x

,

Oracle Database

Participating Experts
2
Points
125
Comments
3

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. ORACLE SYSDATE
    The date and time returned by the function SYSDATE varies when accessing the same database depending on the value of the TWO_TASK environment variable. Case 1) ------- TWO_TASK = < connect string > Observation: SYSDATE returns a date and time different from the dat...
  2. Data warehousing
    can anyone tell me where i can find information on data warehousing, particularly on the process of loading and unloading data from multiple data sources into a single datawarehouse, using Oracle as host database.
  3. ORA-03113 with oracle on NT5
    I formatted my hard drive and tried to install personal oracle 8.1.7 and get the ORA-03113 error when trying to create a database. I reformatted my hard drive and then tried the SE version of oracle, and got the same error again. What do I need to do to get this to work? T...
  4. ORA-03113
    Hello there. Can anyone explain me why i'm getting ORA-03113 when trying to execute the following code from the trigger in application written on Oracle Forms ? In the other hand, this code executes from the SQL+ correctly without any errors. I'm using Oracle Forms version 6....

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: it-rexPosted on 2009-09-22 at 17:45:52ID: 25399142

first check your NLS_DATE_FORMAT and make sure it is working with this line
to_char(add_months(sysdate,-1),'yyyymm')

then you will need to turn sqlnet tracing on to diagnose an error like ORA-03113

*** Set SQL*Net trace for the RMAN client on the target host:

1. Edit sqlnet.ora file and add the following lines:
TRACE_LEVEL_CLIENT=16
TRACE_FILE_CLIENT=sqlnet.trc
TRACE_DIRECTORY_CLIENT=/tmp
TRACE_UNIQUE_CLIENT=on



say that you are using TOAD
2. Find the OS pids of the toad processes in the target instance:

SQL> SELECT S.SID RMAN_SID, P.SPID, S.PROGRAM, S.MODULE
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.PROGRAM LIKE '%TOAD%';

3. Check the trace file

 

by: Suresh_palgharPosted on 2009-09-22 at 23:51:27ID: 25400545

Can you post the outcome when you just have SYSDATE in the WHERE clause (i.e. without ADD_MONTHS function)?
PS_ACCOUNTING_DATE = to_char(sysdate,'yyyymm')

 

by: Mike_BrintonPosted on 2009-09-23 at 12:27:16ID: 25406902

Thanks for your help.  We have found that we need to apply a patch to our 9i database.

*** SESSION ID:(121.22610) 2009-09-22 12:06:21.957
*** 2009-09-22 12:06:21.957
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected  got 
], [], [], [], [], []
Current SQL statement for this session:
SELECT distinct
       p.policy_number phx_policy,
       POL_NUM         whs_policy,
       PS_ACCOUNTING_DATE,
       length(ps_accounting_date) date_len,
       to_char(add_months(SYSDATE,-:"SYS_B_0"),:"SYS_B_1"),
       length(to_char(add_months(SYSDATE,-:"SYS_B_2"),:"SYS_B_3")) date_len_2
  FROM MV_DON_HSB@CLOPHSB,
       policy p   
    WHERE length(trim(p.policy_number)) = :"SYS_B_4"
   --and PS_ACCOUNTING_DATE = to_char(add_months('01-SEP-2009',-1),'yyyymm')
   --and PS_ACCOUNTING_DATE = to_char(add_months(to_date(sysdate,'DD-MON-YY'),-1),'yyyymm')
   and to_char(add_months(sysdate,-:"SYS_B_5"),:"SYS_B_6") = PS_ACCOUNTING_DATE
   AND PS_REINS_TREATY_CO = :"SYS_B_7"
   and pol_num = p.policy_number 
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedmp+327          CALLrel  _ksedst+0            
_ksfdmp.108+14       CALLrel  _ksedmp+0            3
_kgerinv+131         CALLreg  00000000             73B088B0 3
_kgesinv+29          CALLrel  _kgerinv+0           73B088B0 5D6042EC 1DC684C 2
                                                   71EFCEB8
_ksesin+61           CALLrel  _kgesinv+0           73B088B0 5D6042EC 1DC684C 2
                                                   71EFCEB8 1DC684C 2 71EFCEB8
_OCIKSIN+306         CALLrel  _ksesin+0            1DC684C 2 0 3A4 1 32 71EFCED0
_qerrmObnd+329       CALLrel  _OCIKSIN+0           3A4 F66D738 1DC684C
_qerrmOStart+334     CALLrel  _qerrmObnd+0         
_qerrmStart+472      CALLrel  _qerrmOStart+0       
_rwsstd+79           CALL???  00000000             5D68C60C 1
_qerhjStart+143      CALL???  00000000             6FDC9E34 1
_qersoStart+983      CALL???  00000000             
_selexe+610          CALL???  00000000             2 1
_opiexe+4409         CALLrel  _selexe+0            6E434D18 71EFD560 71EFD5D4
_opiall0+2181        CALLrel  _opiexe+0            4 3 71EFD964
_kpoal8+1532         CALLrel  _opiall0+0           5E 22 71EFDADC 0 71EFDB7C
                                                   F66D2A8 29E 24 0 71EFDB68 0 1
                                                   0 0
_opiodr+1398         CALLreg  00000000             5E 14 71EFE334
_ttcpip+3024         CALLreg  00000000             5E 14 71EFE334 0
_opitsk+1907         CALLrel  _ttcpip+0            
_opiino+1480         CALLrel  _opitsk+0            0 0 73B0EB90 2E993EC 135 0
_opiodr+1398         CALLreg  00000000             3C 4 71EFFBD8
_opidrv+563          CALLrel  _opiodr+0            3C 4 71EFFBD8 0
_sou2o+25            CALLrel  _opidrv+0            
_opimai+266          CALLrel  _sou2o+0             
_OracleThreadStart@  CALLrel  _opimai+0            
4+961                                              
77E6482C             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------
 
========== FRAME [1] (_ksedmp+327 -> _ksedst+0) ==========
Dump of memory from 0x71EFCDA8 to 0x71EFCE20
71EFCDA0                   71EFCE20 004EB9F0          [ ..q..N.]
71EFCDB0 00000000 00000000 00000000 00000000  [................]
71EFCDC0 01DC684C 000003A4 FFFFFFFF 000007B2  [Lh..............]
71EFCDD0 73B08D66 71EFCE1C 0288A802 73B0898C  [f..s...q.......s]
71EFCDE0 73B08994 00000000 00000000 01DC684C  [...s........Lh..]
71EFCDF0 00000002 0000000A 73B088B0 5D6042EC  [...........s.B`]]
71EFCE00 01AD65A0 73B08728 71EFCDB4 73B088B0  [.e..(..s...q...s]
71EFCE10 71EFD7FC 01541540 027D506C FFFFFFFF  [...q@.T.lP}.....]
========== FRAME [2] (_ksfdmp.108+14 -> _ksedmp+0) ==========
Dump of memory from 0x71EFCE20 to 0x71EFCE2C
71EFCE20 71EFCE2C 006D137F 00000003           [,..q..m.....]    
========== FRAME [3] (_kgerinv+131 -> 00000000) ==========
Dump of memory from 0x71EFCE2C to 0x71EFCE4C
71EFCE20                            71EFCE4C              [L..q]
71EFCE30 0288A8DD 73B088B0 00000003 67788968  [.......s....h.xg]
 
 
Bug 5015321  OERI[qerrmObnd1] [932] using binds with some functions over a DBLINK
 This note gives a brief overview of bug 5015321. 
 The content was last updated on: 03-APR-2009
 Click here for details of each of the sections below.
Affects:
Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11 
Versions confirmed as being affected	 	9.2.0.8 
 	10.1.0.5 
 	10.2.0.3 
 
Platforms affected	Generic (all / most platforms affected)
Fixed:
This issue is fixed in	 	9.2.0.8 Patch 18 on Windows Platforms 
 	10.2.0.4 (Server Patch Set) 
 	11.1.0.6 (Base Release) 
 
Symptoms:	Related To:
 	Internal Error May Occur (ORA-600) 
 	ORA-600 [qerrmObnd1] 
 	Database Link / Distributed 
 
				
Description
ORa-600 [qerrmObnd1] can occur when using binds with certain functions like 
next_day, or to_date in remote or distributed SQL.
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.
References
Bug 5015321 (This link will only work for PUBLISHED bugs)
Note 245840.1 Information on the sections in this article 
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:

Select allOpen in new window

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...