CREATE TABLE HISTORIAN 
(
  ID NUMBER NOT NULL 
, ITEM_ID NUMBER NOT NULL 
, EVENT_TS DATE DEFAULT SYSDATE 
, value NUMBER 
);

INSERT INTO historian (id,item_id,value) VALUES (1,100, 32);
INSERT INTO historian (id,item_id,value) VALUES (2,100, 18);
INSERT INTO historian (id,item_id,value) VALUES (3,200, 5);
INSERT INTO historian (id,item_id,value) VALUES (4,200, 7);

CREATE TABLE ITEM 
(
  ID NUMBER NOT NULL 
, NAME VARCHAR2(20) NOT NULL 
);
INSERT INTO item (id,name) VALUES (100,'name100');
INSERT INTO item (id,name) VALUES (200,'name200');

create or replace TYPE myGlobType IS OBJECT(
    l_id NUMBER,
    l_name VARCHAR(20),
    value NUMBER     
  );
create or replace TYPE myGlobTable IS TABLE OF myGlobType;

CREATE OR REPLACE FUNCTION MYFUNCTION RETURN myGlobTable AS 
  l_tbl_res myGlobTable:=myGlobTable();
  value_OUT NUMBER;
  
  CURSOR crs IS
  SELECT * FROM ITEM;
  l_row crs%ROWTYPE;
  
  BEGIN
   OPEN crs;
LOOP
FETCH crs INTO l_row;
EXIT WHEN crs%NOTFOUND;
SELECT SUM(value) into value_OUT FROM historian where historian.ITEM_ID=l_row.ID;
l_tbl_res.EXTEND;
l_tbl_res(l_tbl_res.LAST):=myGlobType
(l_row.id,
l_row.name,
value_OUT);
END LOOP;
CLOSE crs;
    RETURN l_tbl_res;
END MYFUNCTION;

SELECT * FROM TABLE(MYFUNCTION());