[askdba] Re: ORA-03113: end-of-file on communication channel
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Sat, 16 Oct 2004 10:45:27 +0100
Chirag,
Didi u check your alert Log .. Sorry if this question has already been
answered...
Cheers
Ganesh
On Sat, 16 Oct 2004 15:13:46 +0530, Chirag DBA <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,PROVIDER_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
>
> On Fri, 15 Oct 2004 12:43:06 -0400, Ankur Shah <ankur_ora@xxxxxxxxxxx> wrote:
> > ORA 3113 has numerous reason...
> >
> > > To start with..is the DB link active I mean tested for connection.
> > > Is firewall involved with connection to the server where db link intends
> > to get data from.
> > > Try doing a simple select over the dblink within the procedure and see if
> > it returns date from psoprdefn@icaeurp1 and then test for each remote
> > tables.
> >
> > ============================
> >
> > HTHU
> >
> > Ankur Shah
> > Oracle DBA
> > DHR-GA
> >
> >
> >
> >
> > ----- Original Message -----
> > From: "Chirag DBA" <ChiragDBA@xxxxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>; <askdba@xxxxxxxxxxxxx>
> > Sent: Friday, October 15, 2004 11:42 AM
> > Subject: [askdba] ORA-03113: end-of-file on communication channel
> >
> > > Hi ,
> > >
> > > I m getting error ' ORA-03113: end-of-file on communication channel '
> > > while creating a procedure which uses database link to populate data.
> > >
> > > Can anyone tell me what can be the problem ?
> > >
> > > - Chirag Majmundar
> > >
> > >
> > > CREATE OR REPLACE procedure populateiCareUsers
> > > is
> > > begin
> > >
> > > -- execute immediate 'drop database link icaeurp1';
> > >
> > > -- create database link icaeurp1
> > > -- connect to act_parts identified by act_parts
> > > -- using
> > > execute immediate 'truncate table temp_profile_users';
> > >
> > > 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;
> > >
> > > insert into profile_users select * from temp_profile_users where oprid
> > > not in(select oprid from profile_users);
> > > end;
> > > --------------------------------------------------------------------------
> > ------------------
> > > ERRORS I M GETTING
> > > --------------------------------------------------------------------------
> > ------------------
> > > CREATE OR REPLACE procedure populateiCareUsers
> > > *
> > > ERROR at line 1:
> > > ORA-03113: end-of-file on communication channel
> > >
> > >
> >
> >
>
>
Other related posts:
