Re: [askdba] Re: ORA-03113: end-of-file on communication channel
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 16 Oct 2004 11:24:03 -0600
Um, what's with the EXECUTE IMMEDIATE 'COMMIT' command? PL/SQL supports the
COMMIT and ROLLBACK commands natively. Also, it is a bad habit to left-pad
or right-pad spaces into the EXECUTE IMMEDIATE ' TRUNCATE TABLE
TEMP_PROFILE_USERS ' command.
The code shows lack of clarity on the nature of the DBMS_OUTPUT package.
All of the strings "printed" using the procedure PUT_LINE are simply
buffered into a text string. If SET SERVEROUTPUT ON is run in SQL*Plus,
then this text string is implicitly allocated/cleared by SQL*Plus at the
start of the procedure call and will be output to standard output by
SQL*Plus implicitly only at the conclusion of the procedure call.
So, outputting messages that say "Please Wait ..." will appear strange as
all of the messages will be seen only at the conclusion of the procedure.
Just an FYI...
As far as diagnosing the ORA-03113, first try commenting out the INSERT
involving the database links, and see if that helps it compile. If
uncommenting the INSERT causes the ORA-03113 again, you may want to look at
the setup of your database links and most particularly attempt to run the
INSERT statement outside of PL/SQL, to see if it works. In other words,
diagnose the database link problems outside of PL/SQL.
Hope this helps...
on 10/16/04 3:43 AM, Chirag DBA at 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 = 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, Updating the Profile_Users table....');
>
> insert into
> profile_users(BEPUSER_ID,ORIGIN,OPRID,LAST_NAME,FIRST_NAME,DESC_DEPT,LOCATION,
> NAME)
> select
>
> SQ_iCAREUSER.nextval,'iCare',OPRID,LAST_NAME,FIRST_NAME,DEPARTMENT,LOCATION,PR
> OVIDER_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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
