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 >