Just like this, sure it can be extended with some checks and other nifty things..:
DECLARE
counter number := 1 ;
-- declare a varchar variable to contain the
-- TO_CHAR string, as DATE type is actually
-- a number
v_date2char varchar2(50) ;
--
CURSOR c_data_ballage
IS
SELECT code
, description
, TO_CHAR(completion, 'YYYY/MM/DD HH24:MI:SS') AS completion
FROM ballage ;
BEGIN
DBMS_OUTPUT.ENABLE(1000000) ;
DBMS_OUTPUT.PUT_LINE('DECLARE') ;
DBMS_OUTPUT.PUT_LINE(' lStart number := 1 ;') ;
DBMS_OUTPUT.PUT_LINE(' CODE_ARRAY dbms_sql.number_table ;') ;
DBMS_OUTPUT.PUT_LINE(' DESCRIPTION_ARRAY dbms_sql.varchar2_table ;') ;
DBMS_OUTPUT.PUT_LINE(' COMPLETION_ARRAY dbms_sql.date_table ;') ;
DBMS_OUTPUT.PUT_LINE('BEGIN') ;
DBMS_OUTPUT.PUT_LINE(' ') ;
FOR data_record IN c_data_ballage LOOP
-- As moment of writing Oracle database considers NULL and empty string ('')
-- as equal, as show in sample below:
-- SQL> SELECT NVL(NULL,'This returns NULL') A,NVL('','This will too') B FROM DUAL;
-- A B
-- ----------------- -------------
-- This returns NULL This will too
IF data_record.completion IS NOT NULL THEN
v_date2char := 'TO_DATE('''||data_record.completion||''', '''||'YYYY/MM/DD HH24:MI:SS'||''')' ;
ELSE
v_date2char := ''''||data_record.completion||'''' ;
END IF ;
DBMS_OUTPUT.PUT_LINE(' CODE_ARRAY('||counter||') := ' || data_record.code ||' ;') ;
DBMS_OUTPUT.PUT_LINE(' DESCRIPTION_ARRAY('||counter||') := '''|| data_record.description ||''' ;') ;
DBMS_OUTPUT.PUT_LINE(' COMPLETION_ARRAY('||counter||') := '|| v_date2char ||' ;') ;
counter := counter + 1 ;
v_date2char := NULL ;
END LOOP ;
DBMS_OUTPUT.PUT_LINE(' LOOP') ;
DBMS_OUTPUT.PUT_LINE(' BEGIN') ;
DBMS_OUTPUT.PUT_LINE(' FORALL i IN lStart..CODE_ARRAY.COUNT') ;
DBMS_OUTPUT.PUT_LINE(' INSERT INTO ballage VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;') ;
DBMS_OUTPUT.PUT_LINE(' EXIT ;') ;
DBMS_OUTPUT.PUT_LINE(' EXCEPTION WHEN OTHERS THEN') ;
DBMS_OUTPUT.PUT_LINE(' lStart := lStart + sql%ROWCOUNT + 1 ;') ;
DBMS_OUTPUT.PUT_LINE(' END ;') ;
DBMS_OUTPUT.PUT_LINE(' END LOOP ;') ;
DBMS_OUTPUT.PUT_LINE('END ;') ;
DBMS_OUTPUT.PUT_LINE('/') ;
END ;
/
This will generate the following output (for my “ballage” table that is):
DECLARE
lStart number := 1 ;
CODE_ARRAY dbms_sql.number_table ;
DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
COMPLETION_ARRAY dbms_sql.date_table ;
BEGIN
CODE_ARRAY(1) := 101 ;
DESCRIPTION_ARRAY(1) := 'WRITING' ;
COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
CODE_ARRAY(2) := 102 ;
DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
CODE_ARRAY(3) := 103 ;
DESCRIPTION_ARRAY(3) := 'SEEDING' ;
COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
CODE_ARRAY(4) := 104 ;
DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
CODE_ARRAY(5) := 105 ;
DESCRIPTION_ARRAY(5) := 'BAWLING' ;
COMPLETION_ARRAY(5) := '' ;
LOOP
BEGIN
FORALL i IN lStart..CODE_ARRAY.COUNT
INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
EXIT ;
EXCEPTION WHEN OTHERS THEN
lStart := lStart + sql%ROWCOUNT + 1 ;
END ;
END LOOP ;
END ;
/
