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 (See ORA-03113.ora-code.com), 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 (See ORA-03113.ora-code.com) 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@(protected) 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@(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, 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 (See ORA-03113.ora-code.com): end-of-file on communication channel
