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: Chirag DBA <ChiragDBA_at_gmail.com>
Date: Sat, 16 Oct 2004 15:13:46 +0530
Message-ID: <1be1d20041016024373a6cdfc@mail.gmail.com>


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...???

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,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_at_hotmail.com> 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_at_icaeurp1 and then test for each remote
> tables.
>
> ============================
>
> HTHU
>
> Ankur Shah
> Oracle DBA
> DHR-GA
>
>
>
>
> ----- Original Message -----
> From: "Chirag DBA" <ChiragDBA_at_gmail.com>
> To: <oracle-l_at_freelists.org>; <askdba_at_freelists.org>
> 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_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;
> >
> > 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
> >
> >
>

>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 16 2004 - 04:39:48 CDT

Original text of this message

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