PL/SQL: ORA-01403 No data found when setting values to complex collection-CodePudding

I'm struggling with adding hours for specific limit in loop. I'd like to set up values from cursor c_hour to collection t_limits_in_plan (for current limit_id), but I'm doing something wrong in below loop.

I'm getting ORA-01403 No data found error, but dbms_output.put_line part is returning values, so there are hours specified for limit

I'm not sure if I can explain it well, so I'll show. I have function like this

    CREATE OR REPLACE FUNCTION testFunction (p_tLimits t_limit) 
    RETURN t_limits_in_plan IS
    
    tLimitsInPlan t_limits_in_plan;
    tLimits       t_limit;
    nLimitId      number;

    
    CURSOR c_hour(LimitID number) IS
                                 SELECT *
                                   FROM hours h
                                  WHERE h.limit_id= LimitID;                 
    
  BEGIN
    tLimits := p_tLimits;
    
    FOR i IN tLimits.FIRST .. tLimits.LAST
    LOOP  
      nLimitId := tLimits(i).id;

      FOR r_hour IN c_hour(nLimitId)
      LOOP
        --I CANNOT MAKE THIS PART TO WORK 
       LimitsInPlan(tLimitsInPlan.last).hour_period_data(NVL(tLimitsInPlan(tLimitsInPlan.last).hour_period_data.last, 0)   1).hour_from := r_hour.hour_from;
        tLimitsInPlan(tLimitsInPlan.last).hour_period_data(tLimitsInPlan(tLimitsInPlan.last).hour_period_data.last).hour_to               := r_hour.hour_to;
        dbms_output.put_line('Limit ' || nLimitId || '. Hours: ' || r_hour.hour_from || ' - ' || r_hour.hour_to);
      END LOOP;
    
    END LOOP;
    RETURN tLimitsInPlan;
  END testFunction;

Types which I'm using are

  TYPE r_limits_in_plan IS RECORD (limit_in_plan_id     number
                                  ,limit                r_limit
                                  ,hour_period_data     t_hour_period_data_tab 
                                  );
 
  TYPE t_limits_in_plan IS TABLE OF r_limits_in_plan INDEX BY binary_integer;                      
  -----------------------------------------------------------------------------------   
  TYPE r_limit IS RECORD(limit_id   number
                        ,ind        varchar2(400)
                        );
    
  TYPE t_limit IS TABLE OF r_limit INDEX BY binary_integer;  
  -----------------------------------------------------------------------------------
  TYPE t_hour_period_data IS RECORD(hour_from  number
                                   ,hour_to    number);  
                           
  TYPE t_hour_period_data_tab IS TABLE OF t_hour_period_data INDEX BY binary_integer;

Edit:

I'm passing below Limit IDs to this functions
Limit ID: 26
Limit ID: 41
Limit ID: 81

So result that I'm expecting is
For LimitID 26 hours 2-3 / 12-15 / 20-23
            41 hours 5-7
            81 no hours

Sample data in hour table

CREATE TABLE hours
(
  hour_from NUMBER not null,
  hour_to   NUMBER not null,
  limit_id  NUMBER(17) not null
)  


INSERT INTO hours (hour_from, hour_to, limit_id) VALUES (12, 13, 23);
INSERT INTO hours (hour_from, hour_to, limit_id) VALUES (2, 3, 26);
INSERT INTO hours (hour_from, hour_to, limit_id) VALUES (12, 15, 26);
INSERT INTO hours (hour_from, hour_to, limit_id) VALUES (20, 23, 26);
INSERT INTO hours (hour_from, hour_to, limit_id) VALUES (5, 7, 41);

Can anyone help? I think I added all requred informations, but if something needs clarification, I'll update

CodePudding user response：

The simplest solution is to pass data in using an SQL defined nested table collection and objects (rather than a PL/SQL defined associative array collection and records) and then you can do all the processing in a single query:

CREATE TYPE r_limit IS OBJECT(
   column1    number
  ,column2    varchar2(400)
  ,column3    varchar2(400)
  ,column4    number
);
    
CREATE TYPE t_limit IS TABLE OF r_limit;

CREATE TYPE t_hour_period_data IS OBJECT(
   hour_from  number
  ,hour_to    number
);
                           
CREATE TYPE t_hour_period_data_tab IS TABLE OF t_hour_period_data;

CREATE TYPE r_limits_in_plan IS OBJECT(
   column1              number
  ,column2              varchar2(400)
  ,column3              varchar2(400)
  ,column4              varchar2(400)
  ,limit                r_limit
  ,hour_period_data     t_hour_period_data_tab 
);
 
CREATE TYPE t_limits_in_plan IS TABLE OF r_limits_in_plan;

You can define the function as:

CREATE FUNCTION testFunction (p_tLimits t_limit) 
RETURN t_limits_in_plan
IS
  tLimitsInPlan t_limits_in_plan;
BEGIN
  SELECT r_limits_in_plan(
           l.column1,
           l.column2,
           l.column3,
           l.column4,
           VALUE(l),
           ( SELECT CAST(
                      COLLECT(
                        t_hour_period_data(
                          h.hour_from,
                          h.hour_to
                        )
                      ) AS t_hour_period_data_tab
                    )
             FROM   hours h
             WHERE  l.column1 = h.limit_id
           )
         )
  BULK COLLECT INTO tLimitsInPlan
  FROM   TABLE(p_tLimits) l;

  RETURN tLimitsInPlan;
END testFunction;
/

Then:

DECLARE
  v_limits t_limit := t_limit(
                        r_limit(26, '26 - Col2', '26 - Col3', 26.4),
                        r_limit(41, '41 - Col2', '41 - Col3', 41.4),
                        r_limit(81, '81 - Col2', '81 - Col3', 81.4)
                      );
  v_lip t_limits_in_plan;
BEGIN
  v_lip := testFunction(v_limits);
  FOR i IN 1 .. v_lip.COUNT LOOP
    DBMS_OUTPUT.PUT( v_lip(i).column1 || ': ' );
    
    FOR j IN 1 .. v_lip(i).hour_period_data.COUNT LOOP
      DBMS_OUTPUT.PUT( v_lip(i).hour_period_data(j).hour_from || '-' );
      DBMS_OUTPUT.PUT( v_lip(i).hour_period_data(j).hour_to || ' / ' );
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
  END LOOP;
END;
/

Outputs:

26: 2-3 / 12-15 / 20-23 / 
41: 5-7 / 
81:

db<>fiddle here