CODEWITHSUNDEEP
oracle-databaseplsqlinsertoracle11gbulkinsert
alejomarchan
alejomarchan

Reputation: 368

Problems using forall bulk insertion

I have the following code which I am trying to do an insert using forall:

DECLARE

START_TIME NUMBER;
END_TIME NUMBER;

CURSOR INSERTA(P_FECHA date) is
SELECT P_FECHA V_MES,GSM, CUENTA,DESC_REGION,UPPER(DESC_TIPO_PROD),FECHA_ACTIVACION,DESC_PLAN,0 V_CANGSM,CODIGO_VIP,DESC_CODIGO_VIP,IDENTIFICACION
  FROM SOPFUN.PA_CONTACTOS_DIGITEL
 WHERE ID_EST_SUSC!='DESACTV';


TYPE T_INSERTA IS TABLE OF INSERTA%ROWTYPE INDEX BY PLS_INTEGER;
V_INSERTA T_INSERTA;


BEGIN

EXECUTE IMMEDIATE ('TRUNCATE TABLE TT_DW_FCT_SEGMENTACION_OPT2');
START_TIME := DBMS_UTILITY.GET_TIME;
OPEN INSERTA(TO_DATE('20140710','YYYYMMDD'));
LOOP
FETCH INSERTA BULK COLLECT INTO V_INSERTA LIMIT 100000;
  FORALL I IN 1..V_INSERTA.Count
    INSERT INTO TT_DW_FCT_SEGMENTACION_OPT2 (V_MES,V_SUSC,V_CUENTA,DESC_REGION ,V_TIPO,V_FACTVN,V_PLAN,V_CANGSM,VIP_CODE,DESC_VIP_CODE,CEDULA)
    VALUES V_INSERTA(i);
  EXIT WHEN INSERTA%NOTFOUND;
END LOOP;
CLOSE INSERTA;
END_TIME := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('BULK INSERT: '||TO_CHAR(END_TIME-START_TIME));

END;

When executing throws me the following error:

Line    Pos Text
24  5   ORA-06550: line 24, column 5:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 24, column 5:
PL/SQL: SQL Statement ignored

Which I do not know why there are missing because they are declared values var type... Can you help me with that?

Upvotes: 0

Views: 249

Answers (2)

Arun
Arun

Reputation: 11

Foall insert can be changed to 

INSERT INTO TT_DW_FCT_SEGMENTACION_OPT2 
VALUES V_INSERTA(i);

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36922

Replace:

VALUES V_INSERTA(i);

with:

VALUES (V_INSERTA(i).v_mes, V_INSERTA(i).gsm, ...);

A faster and easier way would be to use a single SQL statement:

INSERT INTO TT_DW_FCT_SEGMENTACION_OPT2 (V_MES,V_SUSC,V_CUENTA,DESC_REGION ,V_TIPO,V_FACTVN,V_PLAN,V_CANGSM,VIP_CODE,DESC_VIP_CODE,CEDULA)
SELECT date '2014-07-10',GSM, CUENTA,DESC_REGION,UPPER(DESC_TIPO_PROD),FECHA_ACTIVACION,DESC_PLAN,0 V_CANGSM,CODIGO_VIP,DESC_CODIGO_VIP,IDENTIFICACION
  FROM SOPFUN.PA_CONTACTOS_DIGITEL
 WHERE ID_EST_SUSC!='DESACTV';

Upvotes: 1

Related Questions