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

  • From: Chirag DBA <ChiragDBA@xxxxxxxxx>
  • To: tim@xxxxxxxxxxxxx, Nisar Tareen <ntareen@xxxxxxxxx>, askdba@xxxxxxxxxxxxx
  • Date: Mon, 18 Oct 2004 11:58:49 +0530

U r riget tim,

I will update the code, that will definitely help me.

and Nissar,
as u said it can a problem of NETWORK, well, I can run each statement
seperately without any problem. So I cant say that there is a
networking issue with this.

and is there any way that I can get the msg immediately after
executing the something.

I mean all the DBMS_OUTPUT messages comes after the completion of the Procedure.

I wanna give some msg in between finishing each action.

Any alternative of DBMA_OUTPUT??

 - Chirag 


On Sat, 16 Oct 2004 11:24:03 -0600, Tim Gorman <tim@xxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxx 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@icaeurp1 a
> > , psopralias@icaeurp1 b
> > , ps_rd_person_name@icaeurp1 c
> > , ps_location_tbl@icaeurp1 d
> > , ps_dept_tbl@icaeurp1 e
> > , ps_rb_worker@icaeurp1 f
> > , ps_rf_grp_member@icaeurp1 g
> > , ps_rf_provider_grp@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
>

Other related posts: