Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [askdba] Re: ORA-03113: end-of-file on communication channel

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.

CREATE OR REPLACE procedure Cpopulate_iCareUsers is
begin

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US