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

From: Chirag DBA <ChiragDBA_at_gmail.com>
Date: Mon, 18 Oct 2004 16:40:32 +0530
Message-ID: <1be1d200410180410e0901a@mail.gmail.com>

Here you go.. Deepa, Subodh, Nissar

I have put the definition of view for yr information.

If I do insert directly from this query, It gives me error and disconnects the session buddy.
If I m doin with this VIEW, things are goin fine.

And one more question..
Is the anyway to execute the DBMS_OUTPUT package as soon as they occur.

Here I can see all the Output statements once procedure is finished. I wanna see that step by step.

• Chirag Majmundar

CREATE OR REPLACE procedure Cpopulate_iCareUsers is
begin

--Execute immediate 'CREATE OR REPLACE FORCE VIEW BEP.VW_PROFILE_USERS
--(OPRID, LASTNAME, FIRSTNAME, DEPARTMENT, LOCATION,

• PROVIDER_GRP_NAME)
  --AS
  --(
  --select a.oprid
  --, c.last_name
  --, c.first_name
  --, e.descr
  --, d.descr
  --, h.name1
  --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....!!! Deleting data from the Temp table....');
execute immediate 'truncate table temp_profile_users';

DBMS_OUTPUT.PUT_LINE('Deleted data from the Temp table....');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Inserting data in to the Temp table....');

-- inserting data into temp table....

insert into temp_profile_users select * from VW_Profile_Users;
-- inserting data into temp table....

DBMS_OUTPUT.PUT_LINE('Inserted in to the Temp table....');

update temp_profile_users set oprid=rtrim(oprid);
update temp_profile_users set oprid=ltrim(oprid);
update temp_profile_users set last_name=rtrim(last_name);
update temp_profile_users set last_name=ltrim(last_name);
update temp_profile_users set first_name=rtrim(first_name);
update temp_profile_users set first_name=ltrim(first_name);
update temp_profile_users set department=rtrim(department);
update temp_profile_users set department=ltrim(department);
update temp_profile_users set location=rtrim(location);
update temp_profile_users set location=ltrim(location);
update temp_profile_users set PROVIDER_GRP_NAME=rtrim(PROVIDER_GRP_NAME);
update temp_profile_users set PROVIDER_GRP_NAME=ltrim(PROVIDER_GRP_NAME);
DBMS_OUTPUT.PUT_LINE('               


----------------------------------------------------------');

DBMS_OUTPUT.PUT_LINE('                 Updating table
TEMP_PROFILE_USERS1 copy of Profile_Users');
DBMS_OUTPUT.PUT_LINE('               


----------------------------------------------------------');

insert into temp_profile_users1
(BEPUSER_ID,ORIGIN,OPRID,LAST_NAME,FIRST_NAME,DESC_DEPT,LOCATION,NAME) select (SQ_ICAREUSERS.nextval),'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMENT,LOCATION,PROVIDER_GRP_NAME from temp_profile_users where oprid not in(select oprid from temp_profile_users1);
commit;
DBMS_OUTPUT.PUT_LINE('Profile Users from iCare are Updated and Committed........!!! ');
end;
/
--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 18 2004 - 06:06:17 CDT