Re: Problems with PL/SQL

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Tue, 15 Aug 2000 15:49:46 -0400
Message-ID: <39999EDA.B8755DDF@Unforgetable.com>

Andrei Romazanov wrote:
>
> Hi,
>
> what NG can help me by common ORACLE PL/SQL-Problems? For example I want to
> insert from a large table with commit after 1000 records. My first idea
> wasn't correct :-(
>
> declare
> a integer;
> b integer;
> begin
> b:=10;
> for a in 1..1000 loop
> INSERT INTO table_dest SELECT * FROM table_source WHERE rownum between b
> and b + 1000;
> commit;
> b:=b+1000;
> end loop;
> end;
> /
>
> Thanks
>
> Andrei

Keep in mind that I have not tested this so there may be some syntax errors, but it will look something like this:

Declare

    A Integer := 0;
    B Integer := 0;
    More_Records Boolean := True;
    Commit_Point Constant Integer(4) := 1000;     Row_Count Integer(5) := 0;
    Tot_Row_Count Integer(5) := 0;

    Cursor C_Table_Source is
    SELECT *
    FROM TABLE_SOURCE
    where table_key not in (SELECT TABLE_KEY

                            FROM TABLE_TEST);

Begin

    Dbms_Output.Enable(1000000);
    While More_Rows Loop

        For I in C_Table Loop
            Row_Count := C_Table%ROWCOUNT;
            Begin
                INSERT
                INTO TABLE_DEST (COL1,
                                 COL2,
                                 COL3)
                VALUES (I.COL1,
                        I.COL2,
                        I.COL3);
            End;
            If Row_Count >= Commit_Point Then
                Tot_Row_Count := Tot_Row_Count + Row_Count;
                Exit;
            End if;
        End Loop;
        Commit;

    End Loop;
    Dbms_Output.Put_Line('Total rows copied is '||To_Char(Tot_Row_Count));
Exception

    When Others Then

        Raise_Application_Error(-20101,'Fatal error: '||Sqlerrm); End;     

Of course, this isn't the most efficient way to do this. What you really may want to do is use a Snapshot if you are working with Oracle 7 or a Materialized View with Oracle 8/8i. Received on Tue Aug 15 2000 - 14:49:46 CDT