Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

FORALL through scheduler jobs doesn't insert all records

vpolasa5 days ago

I'm trying to create a copy of a very large table and working to achieve this using FORALL and scheduler jobs. But I'm missing something in my script which is causing to miss records after the job is completed (record count in target table doesn't match with the source table). I'm able to replicate this using below example. Can someone please point me what I'm missing or doing incorrectly?

CREATE TABLE test_nolog_src (emp_key NUMBER, dept_num NUMBER, last_name VARCHAR2(100), first_name VARCHAR2(100), product_info VARCHAR2(100));
CREATE TABLE test_nolog_tgt NOLOGGING AS SELECT * FROM test_nolog_src WHERE 1 = 2;

DECLARE
    v_dept_num NUMBER; 
BEGIN 
  FOR I IN 1 .. 3635 LOOP
    IF I < 1200 THEN 
        v_dept_num := 1; 
    ELSIF I BETWEEN 1201 AND 3000 THEN 
        v_dept_num := 2; 
    ELSE 
        v_dept_num := 3; 
    END IF; 
    
    INSERT INTO test_nolog_src (emp_key, dept_num, last_name, first_name, product_info) 
    VALUES (I, v_dept_num, 'last '||I, 'first '||I, 'product details '||I); 
  END LOOP;
END;
/

CREATE OR REPLACE PACKAGE test_log_pkg AS 
PROCEDURE test_log_insert1; 
PROCEDURE test_log_insert2; 
END; 
/

CREATE OR REPLACE PACKAGE BODY test_log_pkg AS 
PROCEDURE test_log_insert1 AS
    TYPE test_array_type IS TABLE OF test_nolog_src%rowtype INDEX BY BINARY_INTEGER;
    test_array_object test_array_type;
    fetch_size NUMBER := 50;
    
    CURSOR test_cursor IS
    SELECT * FROM test_nolog_src WHERE emp_key < 1818; 
BEGIN 
    OPEN test_cursor;
    LOOP
        FETCH test_cursor BULK COLLECT
          INTO test_array_object LIMIT fetch_size;
        EXIT WHEN test_cursor%notfound;

        FORALL I IN INDICES OF test_array_object SAVE EXCEPTIONS
          INSERT/*+ APPEND */ INTO test_nolog_tgt VALUES test_array_object(I);
        COMMIT;
    END LOOP;
    CLOSE test_cursor;
END test_log_insert1;

PROCEDURE test_log_insert2 AS 
    TYPE test_array_type IS TABLE OF test_nolog_src%rowtype INDEX BY BINARY_INTEGER;
    test_array_object test_array_type;
    fetch_size NUMBER := 50;
    
    CURSOR test_cursor IS
    SELECT * FROM test_nolog_src WHERE emp_key > 1817; 
BEGIN 
    OPEN test_cursor;
    LOOP
        FETCH test_cursor BULK COLLECT
          INTO test_array_object LIMIT fetch_size;
        EXIT WHEN test_cursor%notfound;

        FORALL I IN INDICES OF test_array_object SAVE EXCEPTIONS
          INSERT/*+ APPEND */ INTO test_nolog_tgt VALUES test_array_object(I);
        COMMIT;
    END LOOP;
    CLOSE test_cursor;
END test_log_insert2;
END;
/

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'job_testnolog_insert1',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN test_log_pkg.test_log_insert1; END;',
        start_date      => SYSTIMESTAMP,
        enabled         => TRUE
    );
    
    DBMS_SCHEDULER.create_job (
        job_name        => 'job_testnolog_insert2',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN test_log_pkg.test_log_insert2; END;',
        start_date      => SYSTIMESTAMP,
        enabled         => TRUE
    );
END; 
/

Source table records:

SELECT COUNT(1) FROM test_nolog_src;

  COUNT(1)
----------
      3635

Target table records:

SELECT COUNT(1) FROM test_nolog_tgt;

  COUNT(1)
----------
      3600

Using:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Windows 11 Enterprise

This post has been answered by stom on Mar 11 2025
Jump to Answer
Comments
Post Details
Added 5 days ago
8 comments
138 views