INSERT INTO tabname SELECT select_list FROM tab(s) WHERE condition(s)

 INSERT into t_dest VALUES var_sent1

CREATE OR REPLACE procedure carga( a_date IN Varchar2 )
IS
v_sdate_id number;
v_edate_id number;
v_sdate_dt date;
v_edate_dt date;
v_advid number := 1233;
v_year number;
v_cnt number;

cursor cursor_sent1 IS select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;

TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;

var_sent1 tipo_sent1;

BEGIN
v_edate_dt := to_date(a_date,'MM/DD/YYYY');
select year_cal_qy into v_year from t_date where date_dt = v_edate_dt;
select min(date_dt) into v_sdate_dt from t_date where year_cal_qy = v_year;
select date_id into v_sdate_id from t_date where date_dt = v_sdate_dt;
select date_id into v_edate_id from t_date where date_dt = v_edate_dt;


-- This is the original insert .. select
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
select c.id_num, r.placement, r.creative, trunc(date_dt,'IW')+6,
sum(new), sum(cnt), sum(flows)
from t_event a, t_date d, t_atribute c, t_response r
where a.event_date_id = d.date_id
....
and a.event_date_id between v_sdate_id and v_edate_id
group by c.id_num, placement, creative, trunc(date_dt,'IW')+6;
commit;


-- bulk load
OPEN cursor_sent1;
FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
CLOSE cursor_sent1;

--- I have probed of the two forms and I get the same error when compile (Error ORA-00947(not enough values)

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1(id_num, placement, creative, date_dt, new, trades, flows)
VALUES var_sent1(i);

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
INSERT into t_data1
VALUES var_sent1(i);


END;
/ 

SCOTT@orcl_11g> CREATE TABLE t_data1 AS SELECT * FROM DEPT WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1
 12  	 VALUES var_sent1(i);
 13  END;
 14  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC carga

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> 

CREATE TABLE t_data1 AS SELECT * FROM user_objects WHERE 1 = 2
/
CREATE OR REPLACE procedure carga 
IS
  cursor cursor_sent1 IS select * from user_objects WHERE ROWNUM <= 5;
  TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  var_sent1 tipo_sent1;
BEGIN
  OPEN cursor_sent1;
  FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  CLOSE cursor_sent1;
  FORALL i IN var_sent1.FIRST .. var_sent1.LAST
    INSERT into t_data1 
    VALUES var_sent1(i);
END;
/ 
SHOW ERRORS
EXEC carga
SELECT * FROM t_data1
/

SCOTT@orcl_11g> CREATE TABLE t_data1 AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1 (deptno, dname, loc)
 12  	 VALUES var_sent1(i);
 13  END;
 14  /

Warning: Procedure created with compilation errors.

SCOTT@orcl_11g> SHOW ERRORS
Errors for PROCEDURE CARGA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5     PL/SQL: SQL Statement ignored
12/5     PL/SQL: ORA-00947: not enough values
SCOTT@orcl_11g> EXEC carga
BEGIN carga; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.CARGA is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

no rows selected

SCOTT@orcl_11g> CREATE OR REPLACE procedure carga
  2  IS
  3    cursor cursor_sent1 IS select * from dept;
  4    TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  5    var_sent1 tipo_sent1;
  6  BEGIN
  7    OPEN cursor_sent1;
  8    FETCH cursor_sent1 BULK COLLECT INTO var_sent1;
  9    CLOSE cursor_sent1;
 10    FORALL i IN var_sent1.FIRST .. var_sent1.LAST
 11  	 INSERT into t_data1 (deptno, dname, loc)
 12  	 VALUES (var_sent1(i).deptno, var_sent1(i).dname, var_sent1(i).loc);
 13  END;
 14  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC carga

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM t_data1
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> 

FORALL i IN var_sent1.FIRST .. var_sent1.LAST
        INSERT INTO (select id_num, placement, creative, date_dt, new, trades, flows from t_data1) 
        VALUES var_sent1(i);

create or replace procedure p_empleados 
IS
  b1         PLS_INTEGER;
  b2         PLS_INTEGER;
  e1         PLS_INTEGER;
  e2         PLS_INTEGER; 
                           
  cursor cursor_sent1 IS select employee_id, first_name, department_name from employees e, departments d where e.department_id=d.department_id;                            
                    
  TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE;
  
  var_sent1 tipo_sent1;                       
  
BEGIN

    b1 := DBMS_UTILITY.GET_TIME();
    b2 := DBMS_UTILITY.GET_CPU_TIME();

    OPEN  cursor_sent1;
    
    LOOP
    
      FETCH cursor_sent1 BULK COLLECT INTO var_sent1 LIMIT 100;
  
      FORALL i IN var_sent1.FIRST .. var_sent1.LAST
          SAVE EXCEPTIONS
          INSERT INTO (select employee_id, first_name, department_name from t_temporal where 0=1) 
          VALUES var_sent1(i);
          
          commit;
      
      EXIT WHEN cursor_sent1%NOTFOUND;
    
    END LOOP;
    
    CLOSE cursor_sent1;

    e1 := DBMS_UTILITY.GET_TIME() - b1;
    e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;

    DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed BULK/FORALL 100 = ' || ROUND(e1/100, 3) || ' secs.' );
    DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed BULK/FORALL 100 = ' || ROUND(e2/100, 3) || ' secs.' );


    b1 := DBMS_UTILITY.GET_TIME();
    b2 := DBMS_UTILITY.GET_CPU_TIME();

    INSERT into t_temporal(employee_id, first_name, department_name)
    select employee_id, first_name, department_name from employees e, departments d where e.department_id=d.department_id;
    commit;

    e1 := DBMS_UTILITY.GET_TIME() - b1;
    e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;


    DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed INSERT/SELECT = ' || ROUND(e1/100, 3) || ' secs.' );
    DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed INSERT/SELECT = ' || ROUND(e2/100, 3) || ' secs.' );

EXCEPTION
  WHEN OTHERS THEN 
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Found an error at ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||' Error Msg: ' ||       
            SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) );
    END LOOP;                 
END;