I have this plsql block that populates a table with information of two other tables and I have to use a variable array:

DECLARE nombre_grupo VARCHAR2(15);
    direccion_tipo direccion;
    persona_tipo persona;
    personas_array personas := personas();
    CURSOR departamento IS
    SELECT * FROM departamentos;
    CURSOR empleado IS
    SELECT * FROM empleados, departamentos
    WHERE empleados.dept_no = departamentos.dept_no;
    i INTEGER;
BEGIN
    FOR departamento IN (SELECT * FROM departamentos) LOOP
    nombre_grupo := departamento.dnombre;
    i := 1;
    personas_array := personas();
    FOR empleado IN (SELECT * FROM empleados WHERE dept_no = departamento.dept_no) LOOP
        direccion_tipo := DIRECCION(departamento.loc, 'NULL', empleado.dir);
        personas_array(i) := PERSONA(empleado.emp_no, empleado.apellido,
        direccion_tipo, empleado.fecha_alt);
        i := i   1;
    END LOOP;
    INSERT INTO grupos VALUES (nombre_grupo, personas_array);
    END LOOP;
END;

Here's the type personas:

CREATE OR REPLACE TYPE personas AS VARRAY(5) OF PERSONA

So when I execute that block and it reaches the personas_array(i) bit, it exits the execution with "subscript beyond count" error, no matter what value of i. What am I missing?

I've already deleted and created the type personas again, I've also tried creating the type inside the procedure, but it can't insert into the table

CodePudding user response：

A few tips for a SQL beginner:

Don't learn 30 years old Oracle join syntax. Use modern ANSI join syntax, i.e.

SELECT * 
FROM empleados 
   JOIN departamentos ON empleados.dept_no = departamentos.dept_no;

Your cursors are redundant. Either use

DECLARE 
    CURSOR cur_departamento IS
    SELECT * 
    FROM departamentos;   
BEGIN
    FOR departamento IN cur_departamento LOOP
       ...
    END LOOP;
END;

or

DECLARE 
BEGIN
    FOR departamento IN (SELECT * FROM departamentos) LOOP
       ...
    END LOOP;
END;

You can also use this:

DECLARE 
    CURSOR cur_empleados(d IN EMPLEADOS.DEPT_NO%TYPE) IS
    SELECT * 
    FROM EMPLEADOS
    WHERE dept_no = d;

   /*
   -- Do not use this!
    CURSOR cur_empleados(dept_no IN EMPLEADOS.DEPT_NO%TYPE) IS
    SELECT * 
    FROM EMPLEADOS
    WHERE EMPLEADOS.dept_no = dept_no;  -> will return all rows
    */    

BEGIN
   FOR departamento IN (SELECT * FROM departamentos) LOOP
      FOR empleado  IN cur_empleados(departamento.dept_no) LOOP
         ...
      END LOOP;
   END LOOP;
END;

According to my feelings, VARRAYs are often part of student material but hardly used in real life.

Using string 'NULL' is most likely not want you want. Use literal NULL, i.e.

DIRECCION(departamento.loc, NULL, empleado.dir)

Type VARRAY(5) OF PERSONA defines a varray with maximum size of 5 elements. When you initialize it with personas_array := personas(); then the actual size is 0. You need to extend the varray.

You code may look like this:

DECLARE 

    nombre_grupo VARCHAR2(15); 
    direccion_tipo direccion;
    persona_tipo persona;
    personas_array personas;

    i INTEGER;
    
BEGIN
    FOR departamento IN (SELECT * FROM departamentos) LOOP
        nombre_grupo := departamento.dnombre;
        i := 1;
        personas_array := personas();
        FOR empleado IN (SELECT * FROM empleados WHERE dept_no = departamento.dept_no AND ROWNUM <= 5) LOOP
            direccion_tipo := DIRECCION(departamento.loc, NULL, empleado.dir);
            personas_array.extend();
            personas_array(i) := PERSONA(empleado.emp_no, empleado.apellido, direccion_tipo, empleado.fecha_alt);
            i := i   1;
        END LOOP;
        INSERT INTO grupos VALUES (nombre_grupo, personas_array);
    END LOOP;
END;

Just a note, such procedure would have rather low performance. The professional way of doing it would be a Nested Table and then insert the data with a single command:

CREATE OR REPLACE TYPE personas_NT AS TABLE OF PERSONA;

INSERT INTO grupos VALUES (nombre_grupo, personas_array)
SELECT dnombre, 
    CAST(MULTISET(
        SELECT 
            emp_no, 
            apellido, 
            DIRECCION(dept.loc, NULL, dir), 
            fecha_alt
        FROM EMPLEADOS
        WHERE dept_no = dept.dept_no
    ) AS personas_NT) AS personas_array
FROM DEPARTAMENTOS dept;

But maybe, that would be a chapter in the "advanced" SQL course.