Posted on 2008-11-17
Last Modified: 2013-12-19
Please note this query is resulting an output in Oracle 10G but the same is not working with Oracle 9i

SELECT ProviderName, ProviderID, Count(ProviderID) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
FROM ReferralView
GROUP BY ProviderName, ProviderID;
I am given the below error trying to execute this query from the sql prompt and the session gets terminated.
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Please update

Thank you

Question by:suryapinna
    LVL 27

    Accepted Solution

    Try these two alternatives.

    SELECT ProviderName, ProviderID, Count(*) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
    FROM ReferralView
    GROUP BY ProviderName, ProviderID;
    SELECT ProviderName, ProviderID, Count(*) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
    SELECT ProviderName, ProviderID, SubmitDate, rownum r
    FROM ReferralView
    GROUP BY ProviderName, ProviderID;

    Open in new window

    LVL 6

    Expert Comment

    Apparently the error message is nothing to do with the given multiple aggregate functions in the SQL.
    The error ORA-03113 occurs when an unexpected end-of-file occurred during the communication channel. There should be something else that is causing a communication failure between the server and your oracle client.

    Are you using Unix or Linux environments where the environment variable TWO_TASK is set specifying that you are connecting to a remote server?

    This error could also occur if the shadow two-task process associated with the connection terminated abnormally.

    Let me check if there is any reference could be found on ORACLE and get back to you.

    Good luck :)

    LVL 6

    Assisted Solution

    Here it is, per the ORACLE documentation and description given for ORA-03113

    ORA-03113 end-of-file on communication channel
    Cause: An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8 two-task software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down.

    In addition, this message could occur when any of the following statements/commands have been issued:

    Action: If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.

    Hope this helps.
    Good luck :)
    LVL 27

    Expert Comment

    >> ORA-03113: end-of-file on communication channel

    Yes you are right.
    It is a very vague error, which could be resulted from N number of reasons.

    Most of the times, it could be becuase of a bug and it may require patching.
    The best resolution is - to contact Oracle support. They can dig your trace files and log files to locate the actual problem.
    LVL 28

    Assisted Solution

    suryapinna: it would be good if you contact your DBA first for this error and then they can decide whether they need to go for oracle support or they can resolve it by themselves.

    As updated already, query is fine and it has to work fine even in 9i. So request DBA's to have a look as to why you are getting ora-03113

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now