[askdba] Re: ORA-03113: end-of-file on communication channel
- From: Nisar Tareen <ntareen@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Sat, 16 Oct 2004 11:39:38 -0700 (PDT)
Chiku,
If this is the case , Why don't you use External table... Much easier may
and very dynamic. In your procedure you have to keep changing the procedure
each time the data will be chnaged.
What did you say. ?
Nisar Tareen
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 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"
> To: ;
> 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
> >
> >
>
>
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
Other related posts:
