[askdba] Re: ORA-03113: end-of-file on communication channel
- From: Chirag DBA <ChiragDBA@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Mon, 18 Oct 2004 16:40:32 +0530
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@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....!!! 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;
/
Other related posts:
