[askdba] Re: ORA-03113: end-of-file on communication channel
- From: "Deepa S. Kale" <deepask@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Mon, 18 Oct 2004 12:59:15 +0530
chirag can u send me only stmt and pl/sql code also...
u get probelm in compiling or in executing pl/sql ....?
Deepa Kale -DBA
Jyoti Structures - Nasik(IT)
Chirag DBA
<ChiragDBA@gmail.
com> To
Sent by: askdba@xxxxxxxxxxxxx
askdba-bounce@fre cc
elists.org tim@xxxxxxxxxxxxx, Nisar Tareen
<ntareen@xxxxxxxxx>,
"Oracle-L@Freelists. Org (E-mail)"
18-10-2004 12:49 <oracle-l@xxxxxxxxxxxxx>
PM Subject
[askdba] Re: ORA-03113: end-of-file
on communication channel
Please respond to
askdba@freelists.
org
Thank you Subodh,
but Cursor is very slow I think. My client has asked me not to use
cursor as it will take lot many time to execute.
and I tried running each statement seperately, goes fine.
I think I need to change some parameter in SQLNET.ORA Im sure but what
parameter and what value not sure. I was getting this error in
previous company where I was working as a Production DBA and did
something in SQLNET.ORA and resolved. I need to check it again anyhow.
Im getting all the outputs after the completion of the Procedure. Any
idea to get it as and when they occur.
- Chirag Majmundar
On Mon, 18 Oct 2004 02:12:19 -0500, Subodh Deshpande
<subodh_deshpande@xxxxxxxxx> wrote:
> hello chirag,
>
> end-of-file communication is due to network, as you are saying there is
no =
> problem with network.
> then check the joins the table joins, i feel the joins are not
precise..or =
> go for subquery..
> too many corss joins are happening i suppose..
> a=3Db, b=3Dc, b=3Df, b=3Dg, f=3Dd,f=3De, g=3Dh are these correct..
>
> > > > 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;
>
> what happens if you execute only select part..
>
> are you inserting all rows of a to h tables into temp_profile..
>
> if you are using pl/sql then why are you not using cursor to select the
row=
> s then insert in a loop..
>
> to monitor row execution put dbms_output in insert loop..
>
> e.g.=20
> declare cursor c1 as select... from a to g where conditions
>
> column declarations...
> =20
> begin
> open c1
> loop
> fetch c1 into... fetch column list
> exit when c1%notfound;
> begin
> insert into temp_profile values(fetch column lists);
> dbms_output.put_line(fetch column list);
> commit;
> end;
> end loop;
> close c1;
> end;
>
> ps : some syntex may be here are there..this just simple, basic example..
> set serverouput on; will show only few rows..on set serverput on size
=
> 100000; etc will show more rows..on screen..
>
> take care..subodh
> ----- Original Message -----
> From: Chirag DBA <ChiragDBA@xxxxxxxxx>
> To: tim@xxxxxxxxxxxxx, Nisar Tareen <ntareen@xxxxxxxxx>,
askdba@xxxxxxxxxxx=
> rg
> Subject: [askdba] Re: ORA-03113: end-of-file on communication channel
> Date: Mon, 18 Oct 2004 11:58:49 +0530
>
> >=20
> > U r riget tim,
> >=20
> > I will update the code, that will definitely help me.
> >=20
> > 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.
> >=20
> > and is there any way that I can get the msg immediately after
> > executing the something.
> >=20
> > I mean all the DBMS_OUTPUT messages comes after the completion of the
Pro=
> cedure.
> >=20
> > I wanna give some msg in between finishing each action.
> >=20
> > Any alternative of DBMA_OUTPUT??
> >=20
> > - Chirag=20
> >=20
> >=20
> > 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
support=
> s 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.
> > >=20
> > > 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.
> > >=20
> > > So, outputting messages that say "Please Wait ..." will appear
strange =
> as
> > > all of the messages will be seen only at the conclusion of the
procedur=
> e.
> > > Just an FYI...
> > >=20
> > > 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
loo=
> k at
> > > the setup of your database links and most particularly attempt to run
t=
> he
> > > INSERT statement outside of PL/SQL, to see if it works. In other
words,
> > > diagnose the database link problems outside of PL/SQL.
> > >=20
> > > Hope this helps...
> > >=20
> > >=20
> > > on 10/16/04 3:43 AM, Chirag DBA at ChiragDBA@xxxxxxxxx wrote:
> > >=20
> > > > 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,=
> LOCATION,
> > > > NAME)
> > > > select
> > > >
> > > >
SQ_iCAREUSER.nextval,'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMENT,LO=
> CATION,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
> > >=20
> > > --
> > >=20
> > >=20
> > > //www.freelists.org/webpage/oracle-l
> > >
> >=20
> >=20
>
> Good Luck..Subodh Deshpande
>
>
=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=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Experience Is Knowledge
> Wisdom Is Philosophy
>
=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=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> --=20
> _______________________________________________
> Find what you are looking for with the Lycos Yellow Pages
>
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as=
> p?SRC=3Dlycos10
>
>
Other related posts:
