CREATE OR REPLACE TYPE O_T_emplo AS OBJECT (
L_o_first_name VARCHAR2(30),
L_o_last_name  VARCHAR2(30),
L_o_depar      NUMBER,
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2(30),
L_o_last_name  VARCHAR2(30),
L_o_depar      NUMBER)
RETURN SELF AS RESULT
);
/
CREATE OR REPLACE TYPE BODY O_T_emplo IS
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2(30),
L_o_last_name  VARCHAR2(30),
L_o_depar      NUMBER
) RETURN SELF AS RESULT IS
BEGIN
SELF.L_o_first_name := L_o_first_name;
SELF.L_o_last_name  := L_o_last_name;
self.L_o_depar      := L_o_depar;
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE tab_obj AS TABLE OF O_T_emplo;
/

Use it plsql:

DECLARE
  tab_emps tab_obj;
  info_emps O_T_emplo;
  CURSOR C_infos IS 
    SELECT e.first_name, e.last_name, e.department_id 
    FROM employees e;
  infos C_infos%ROWTYPE;
BEGIN
  tab_emps := tab_obj();
  OPEN C_infos;
  LOOP
    FETCH C_infos INTO infos;
    info_emps := O_T_emplo(infos.first_name, infos.last_name, infos.department_id);
    tab_emps.extend();
    tab_emps(tab_emps.LAST) := info_emps;
    EXIT WHEN C_infos%NOTFOUND;
  END LOOP;  
  CLOSE C_infos;
END;
/

Can anyone help me?

CodePudding user response：

• In the type, remove the length from VARCHAR2 data types in the signature of the constructor in the declaration and the body.
• In the PL/SQL block, change the order in the loop so you EXIT immediately after the FETCH; if you do it the other way round then the final row of the cursor will be inserted twice.
• Optionally, you can remove the info_emps intermediate variable.

CREATE OR REPLACE TYPE O_T_emplo AS OBJECT (
  L_o_first_name VARCHAR2(30),
  L_o_last_name  VARCHAR2(30),
  L_o_depar      NUMBER,

  CONSTRUCTOR FUNCTION O_T_emplo(
    L_o_first_name VARCHAR2,
    L_o_last_name  VARCHAR2,
    L_o_depar      NUMBER
  ) RETURN SELF AS RESULT
)
/

CREATE OR REPLACE TYPE BODY O_T_emplo IS
  CONSTRUCTOR FUNCTION O_T_emplo(
    L_o_first_name VARCHAR2,
    L_o_last_name  VARCHAR2,
    L_o_depar      NUMBER
  ) RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.L_o_first_name := L_o_first_name;
    SELF.L_o_last_name  := L_o_last_name;
    self.L_o_depar      := L_o_depar;
    RETURN;
  END;
END;
/
CREATE OR REPLACE TYPE tab_obj AS TABLE OF O_T_emplo;
/

Then:

DECLARE
  tab_emps tab_obj;
  CURSOR C_infos IS SELECT e.first_name, e.last_name, e.department_id FROM employees e;
  infos C_infos%ROWTYPE;
BEGIN
  tab_emps := tab_obj();
  OPEN C_infos;
  LOOP
    FETCH C_infos INTO infos;
    EXIT WHEN C_infos%NOTFOUND;
    tab_emps.extend();
    tab_emps(tab_emps.LAST) := O_T_emplo(
      infos.first_name,
      infos.last_name,
      infos.department_id
    );
  END LOOP;  
  CLOSE C_infos;
  
  FOR i IN 1 .. tab_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      tab_emps(i).l_o_first_name || ', ' ||
      tab_emps(i).l_o_last_name || ', ' ||
      tab_emps(i).l_o_depar
      
    );
  END LOOP;
END;
/

db<>fiddle here