Insert massive random with Bulk collect and forall in oracle pl / sql

0

for simulation questions I am trying a user table with at least 6 million records more quickly using bulk collect and forall, the codes that adapt this worked only once and then I did not understand why it stopped working. here I leave the code used

DECLARE
TYPE matriz_rowid IS TABLE OF ROWID;
TYPE matriz_USU_CI IS TABLE OF usuario.USU_CI%TYPE;
TYPE matriz_USU_P_NOMBRE IS TABLE OF usuario.USU_P_NOMBRE%TYPE;
TYPE matriz_USU_S_NOMBRE IS TABLE OF usuario.USU_S_NOMBRE%TYPE;
TYPE matriz_USU_P_APELLIDO IS TABLE OF usuario.USU_P_APELLIDO%TYPE;
TYPE matriz_USU_S_APELLIDO IS TABLE OF usuario.USU_S_APELLIDO%TYPE;
TYPE matriz_USU_FECHA_NAC IS TABLE OF usuario.USU_FECHA_NAC%TYPE;
TYPE matriz_USU_PASAPORTE IS TABLE OF usuario.USU_PASAPORTE%TYPE;
TYPE matriz_USU_PROFESION IS TABLE OF usuario.USU_PROFESION%TYPE;
TYPE matriz_USU_TELEFONO IS TABLE OF usuario.USU_TELEFONO%TYPE;
TYPE matriz_USU_CORREO IS TABLE OF usuario.USU_CORREO%TYPE;  

CURSOR cur IS SELECT rowid,USU_ci,USU_p_nombre,USU_S_NOMBRE,USU_P_apellido,USU_S_apellido,USU_fecha_nac,USU_pasaporte,USU_profesion,USU_telefono,USU_correo
    FROM usuario;
    m_rowid matriz_rowid;
    m_USU_CI matriz_USU_CI;
    m_USU_P_NOMBRE matriz_USU_P_NOMBRE;
    m_USU_S_NOMBRE matriz_USU_S_NOMBRE;
    m_USU_P_APELLIDO matriz_USU_P_APELLIDO;
    m_USU_S_APELLIDO matriz_USU_S_APELLIDO;
    m_USU_FECHA_NAC matriz_USU_FECHA_NAC;
    m_USU_PASAPORTE matriz_USU_PASAPORTE;
    m_USU_PROFESION matriz_USU_PROFESION;
    m_USU_TELEFONO matriz_USU_TELEFONO;
    m_USU_CORREO matriz_USU_CORREO;
    contador NUMBER := 100;

BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT
      INTO m_rowid,m_USU_ci,m_USU_p_nombre,m_USU_S_NOMBRE,m_USU_P_apellido,m_USU_S_apellido,m_USU_fecha_nac,m_USU_pasaporte,m_USU_profesion,m_USU_telefono,m_USU_correo LIMIT contador;
    FOR i IN 1 .. m_rowid.count
    LOOP
        m_USU_CI(i) := i;
        m_USU_P_NOMBRE(i) := DBMS_RANDOM.STRING('z',8);
        m_USU_S_NOMBRE(i) := DBMS_RANDOM.STRING('z',8);
        m_USU_P_APELLIDO(i) := DBMS_RANDOM.STRING('z',8);
        m_USU_S_APELLIDO(i) := DBMS_RANDOM.STRING('z',8);
        m_USU_FECHA_NAC(i) := TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J'),TO_CHAR(DATE '1950-12-31','J'))),'J');
        m_USU_PASAPORTE(i) := NULL;
        m_USU_PROFESION(i) := DBMS_RANDOM.STRING('z',10);
        m_USU_TELEFONO(i) := telefonos(telefono('Celular',floor(dbms_random.value(1000000,9999999))),telefono('Casa',floor(dbms_random.value(1000000,9999999))));
        m_USU_CORREO(i) := correos(DBMS_RANDOM.STRING('z',10)||'@gmail.com');
    END LOOP;
    FORALL i IN 1 .. m_rowid.count
      UPDATE usuario
        SET USU_CI = m_USU_CI(i),
            USU_P_NOMBRE = m_USU_P_NOMBRE(i),
            USU_S_NOMBRE = m_USU_S_NOMBRE(i),
            USU_P_APELLIDO = m_USU_P_APELLIDO(i),
            USU_S_APELLIDO = m_USU_S_APELLIDO(i),
            USU_FECHA_NAC = m_USU_FECHA_NAC(i),
            USU_PASAPORTE = m_USU_PASAPORTE(i),
            USU_PROFESION = m_USU_PROFESION(i),
            USU_TELEFONO = m_USU_TELEFONO(i),
            USU_CORREO = m_USU_CORREO(i)
        WHERE rowid = m_rowid(i);
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

in advance thank you very much for your help.

    
asked by Bastvai 31.07.2018 в 23:48
source

0 answers