Re: ORA-03113: end-of-file on communication channel 2004-10-18 - By Chirag DBA
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@(protected) a
--, psopralias@(protected) b
--, ps_rd_person_name@(protected) c
--, ps_location_tbl@(protected) d
--, ps_dept_tbl@(protected) e
--, ps_rb_worker@(protected) f
--, ps_rf_grp_member@(protected) g
--, ps_rf_provider_grp@(protected) 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
|
|
