A PL/SQL Procedure to Insert Data From Another Table

Dear Experts,

I would like to write a procedure to insert data from Table A to Table B. (Table A and Table B are large tables with hundreds of millions of rows) Table A is a normal table and has fields x,y,z; Table B is a Daily partitioned table and has fields x,y,z,t,k.

The procedure should commit for each 10.000 rows insert and call another procedure PRX in loop. Duplicate values will simply be ignored and operation will continue as well.

Could you please help me?

BR
GurcanKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
Something like

PROCEDURE process_all_rows (commit_limit IN PLS_INTEGER DEFAULT 10000)
IS
    CURSOR c_A
    IS
        SELECT x, y, z
        , null t   -- depending on t type change to to_date(null) t  or to_number(number) t to match B-definition
        , null k   -- depending on k type change to to_date(null) k  or to_number(number) k to match B-definition
        FROM A;

    TYPE B_tab_type IS TABLE OF A%ROWTYPE
        INDEX BY PLS_INTEGER;

    B_tab B_tab_type;
   
BEGIN  
    OPEN c_A;
    LOOP
        FETCH c_A
            BULK COLLECT INTO B_tab LIMIT commit_limit;  -- fetch data into collection

        --  calculation into collection data
        FOR indx IN 1 .. B_tab.COUNT
        LOOP
            PRX(B_tab(indx).x, B_tab(indx).y, B_tab(indx).z,B_tab(indx).t,tab(indx).k)); -- assuming PRX is a procedure calculating t an k for a given x,y,x
        END LOOP;

        --  insert from collection
        FORALL indx IN 1 .. B_tab.COUNT
        INSERT INTO B (B_tab(indx));

        COMMIT;
        EXIT WHEN B_tab.COUNT < commit_limit;

   END LOOP;

   CLOSE c_A;
END process_all_rows;

But with hundred of millions of rows ?  What should happen if the proces stops after one millions rows (rollback segment too old, database down etc).  If you know a way to resume operations without filling B twice then it is probably advisible to use that same method to  avoid the selection of millions of rows  in one pass.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.