Bulk into table objects

Question

I want populate my table of objects and I try of different ways but I can´t. With this code I have an extensive error: "Encountered the symbol "L_T_O_TYPE" when expecting one of the following: .(*@_-+/", it is a big message

CREATE OR REPLACE TYPE O_Type AS OBJECT (   
    depar_id     NUMBER,
    depar_name   VARCHAR2(20),
    man_id       NUMBER,
    loca_id      NUMBER,
    CONSTRUCTOR FUNCTION O_Type(
        depar_id   NUMBER,
        depar_name VARCHAR2,
        man_id     NUMBER,
        loca_id    NUMBER)
        RETURN SELF AS RESULT 
);

CREATE OR REPLACE TYPE BODY O_Type IS 
    CONSTRUCTOR FUNCTION O_Type(
    depar_id   NUMBER,
    depar_name VARCHAR2,
    man_id     NUMBER,
    loca_id    NUMBER,
    )RETURN AS SELF AS RESULT IS
    BEGIN
        self.depar_id   := depar_id;
        self.depar_name := depar_name;
        self.man_id     := man_id;
        self.loca_id    := loca_id;
        RETURN;
    END;
END;
/
CREATE OR REPLACE TYPE T_o_type AS TABLE OF O_Type;
/
DECLARE
    CURSOR C_depar IS SELECT *
                       FROM departments;                       
    TYPE T_C_DEPAR IS TABLE OF C_depar%ROWTYPE;
    L_TABLE T_C_DEPAR;
    l_T_o_type T_o_type;
BEGIN
    l_T_o_type := T_o_type();
    OPEN C_depar;
    LOOP
        FETCH C_depar BULK COLLECT INTO L_TABLE;
        EXIT WHEN C_depar%NOTFOUND; 
        
        FORALL i IN 1..L_TABLE.count
            l_T_o_type(i) : = T_o_type(L_TABLE.DEPARTMENT_ID, L_TABLE.DEPARTMENT_NAME, L_TABLE.MANAGER_ID ,  L_TABLE.LOCATION_ID)
     
    END LOOP;
    CLOSE C_depar;
    
END;
/

Can someone tell me the best way to populate my object table?

Answer 1

"Best" is subjective.

---

If you want all the rows in a single collection, use SELECT ... BULK COLLECT INTO:

DECLARE
    l_depts T_o_type;
BEGIN
  SELECT O_Type(id, name, man_id, loc_id)
  BULK COLLECT INTO l_depts
  FROM   departments;
  
  FOR i IN 1 .. l_depts.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      l_depts(i).depar_id
      || ' ' || l_depts(i).depar_name
      || ' ' || l_depts(i).man_id
      || ' ' || l_depts(i).loca_id
    );
  END LOOP;
END;
/

---

If you want to process the rows in batches then you can use:

DECLARE
  CURSOR C_depar IS
    SELECT O_Type(id, name, man_id, loc_id)
    FROM   departments;                       
  
  l_depts T_o_type;
BEGIN
  OPEN C_depar;
  LOOP
    FETCH C_depar BULK COLLECT INTO L_depts LIMIT 10;
    FOR i IN 1 .. l_depts.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        l_depts(i).depar_id
        || ' ' || l_depts(i).depar_name
        || ' ' || l_depts(i).man_id
        || ' ' || l_depts(i).loca_id
      );
    END LOOP;
    EXIT WHEN C_depar%NOTFOUND; 
  END LOOP;
  CLOSE C_depar;
END;
/

db<>fiddle here