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

  • From: "Vinod Gopinath BMMI IS" <vinodg@xxxxxxxxxxx>
  • To: <askdba@xxxxxxxxxxxxx>
  • Date: Sat, 16 Oct 2004 13:06:51 +0300

Chirag,
Did u check the user_dump directory. Is there any log created.
/- Vin
-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Chirag DBA
Sent: Saturday, October 16, 2004 12:57 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: ORA-03113: end-of-file on communication channel

yes Ganesh,

I have checked the alert log again. No problems found.

All the statements runs seperately preety well. else it should gimme
error in SQL itself if not running successfully.

 - Chirag=20


On Sat, 16 Oct 2004 10:45:27 +0100, Ganesh Raja <ganesh.raja@xxxxxxxxx>
wrote:
> Chirag,
>=20
> Didi u check your alert Log .. Sorry if this question has already been
> answered...
>=20
> Cheers
> Ganesh
>=20
>=20
>=20
>=20
> 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 =3D b.oprid
> > and b.person_id =3D c.person_id
> > and f.person_id =3D b.person_id
> > and f.location=3D d.location
> > and f.deptid =3D e.deptid
> > and g.person_id =3D b.person_id
> > and g.provider_grp_id =3D 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,LOC
ATION,NAME)
> > select
> >
> >
SQ_iCAREUSER.nextval,'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMENT,LOCAT
ION,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.
> > >
> > > =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
> > >
> > > 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 =3D b.oprid
> > > > and b.person_id =3D c.person_id
> > > > and f.person_id =3D b.person_id
> > > > and f.location=3D d.location
> > > > and f.deptid =3D e.deptid
> > > > and g.person_id =3D b.person_id
> > > > and g.provider_grp_id =3D 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
> > > >
> > > >
> > >
> > >
> >
> >
>=20
>


Other related posts: