Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [askdba] Re: ORA-03113: end-of-file on communication channel

Re: [askdba] Re: ORA-03113: end-of-file on communication channel

From: Tim Gorman <tim_at_sagelogix.com>
Date: Sat, 16 Oct 2004 11:24:03 -0600
Message-ID: <BD96B553.1E171%tim@sagelogix.com>


Um, what's with the EXECUTE IMMEDIATE 'COMMIT' command? PL/SQL supports the COMMIT and ROLLBACK commands natively. Also, it is a bad habit to left-pad or right-pad spaces into the EXECUTE IMMEDIATE ' TRUNCATE TABLE TEMP_PROFILE_USERS ' command.

The code shows lack of clarity on the nature of the DBMS_OUTPUT package. All of the strings "printed" using the procedure PUT_LINE are simply buffered into a text string. If SET SERVEROUTPUT ON is run in SQL*Plus, then this text string is implicitly allocated/cleared by SQL*Plus at the start of the procedure call and will be output to standard output by SQL*Plus implicitly only at the conclusion of the procedure call.

So, outputting messages that say "Please Wait ..." will appear strange as all of the messages will be seen only at the conclusion of the procedure. Just an FYI...

As far as diagnosing the ORA-03113, first try commenting out the INSERT involving the database links, and see if that helps it compile. If uncommenting the INSERT causes the ORA-03113 again, you may want to look at the setup of your database links and most particularly attempt to run the INSERT statement outside of PL/SQL, to see if it works. In other words, diagnose the database link problems outside of PL/SQL.

Hope this helps...

on 10/16/04 3:43 AM, Chirag DBA at ChiragDBA_at_gmail.com wrote:

> Each individual statements are working fine. But when I put them in
> the PROCEDURE, it throws me out n disconnects the session.
>
> I m inserting all data into temporary table n don wanna do filter on DB Link.
> after putting them in temporary table I m inserting into my original
> table with filtering.
>
> No firewall on DB as I m able to run the SQLs seperately.
>
> Any Idea why it gives me this error...???
>
> - Chirag Majmundar
>
> create or replace procedure populateiCareUsers IS
> begin
> dbms_output.put_line('Please Wait Deleting data from the Temp table');
> -- delete from temp_profile_users;
> execute immediate ' TRUNCATE TABLE TEMP_PROFILE_USERS ';
> dbms_output.put_line('-------------------------------------------');
> dbms_output.put_line('Deleted data from the Temp table....');
> dbms_output.put_line('-------------------------------------------');
>
> dbms_output.put_line('-------------------------------------------');
>
> dbms_output.put_line('Inserting data in to the Temp table....');
> dbms_output.put_line('-------------------------------------------');
>
> insert into temp_profile_users
> select a.oprid
> , c.last_name
> , c.first_name
> , e.descr department
> , d.descr location
> , h.name1 provider_grp_name
> from psoprdefn_at_icaeurp1 a
> , psopralias_at_icaeurp1 b
> , ps_rd_person_name_at_icaeurp1 c
> , ps_location_tbl_at_icaeurp1 d
> , ps_dept_tbl_at_icaeurp1 e
> , ps_rb_worker_at_icaeurp1 f
> , ps_rf_grp_member_at_icaeurp1 g
> , ps_rf_provider_grp_at_icaeurp1 h
> where a.oprid = b.oprid
> and b.person_id = c.person_id
> and f.person_id = b.person_id
> and f.location= d.location
> and f.deptid = e.deptid
> and g.person_id = b.person_id
> and g.provider_grp_id = h.provider_grp_id;
>
> dbms_output.put_line('Please Wait, Updating the Profile_Users table....');
>
> insert into
> profile_users(BEPUSER_ID,ORIGIN,OPRID,LAST_NAME,FIRST_NAME,DESC_DEPT,LOCATION,
> NAME)
> select
>
> SQ_iCAREUSER.nextval,'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMENT,LOCATION,PR
> OVIDER_GRP_NAME
> from temp_profile_users where
>
> oprid not in(select oprid from profile_users);
>
> EXECUTE IMMEDIATE 'commit';
> dbms_output.put_line('Profile Users from iCare are Updated and
> Committed........!!! ');
> end;
> /
>
> create or replace procedure populateiCareUsers IS
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 16 2004 - 12:23:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US