Как получить элемент n из вложенной таблицы pl/sql?

Вы не можете назначать переменные с помощью оператора select из коллекции в цикле, как показано ниже.

SELECT productLine, MinimumPrice INTO prodLine, prodPrice
FROM TABLE(cartLineList(i));

На элементы коллекции нельзя ссылаться внутри оператора SELECT 1 на 1 с помощью цикла. Вы можете прокручивать коллекцию как

For i in 1..collection.count
loop
 ...
 ..
End loop;

Коллекция имеет несколько строк, и когда вы это делаете, вы пытаетесь присвоить много строк одной переменной, что неправильно. Вы можете сделать любой из ниже объясненных. Там соответствующее объяснение встроено.

CREATE OR REPLACE PROCEDURE GETOFFERINGRECOMMENDATION (
     CARTLINELIST     IN       CARTLINETYPE,
     RECOMMENDATION   OUT      SYS_REFCURSOR)
IS
     TYPE V_PRODLINE IS TABLE OF VARCHAR2 (20)
          INDEX BY PLS_INTEGER;

     TYPE V_PRODPRICE IS TABLE OF NUMBER (5, 0)
          INDEX BY PLS_INTEGER;

     PRODLINE                      V_PRODLINE;
     PRODPRICE                     V_PRODPRICE;
BEGIN
    --Putting the collection result to another collection
     SELECT PRODUCTLINE,
            MINIMALPRICE
     BULK COLLECT INTO PRODLINE,
             PRODPRICE
       FROM TABLE (CARTLINELIST);

     -- Assuming number of elements will be same in both prodLine, prodPrice colection, loop can be iterated as below
     FOR I IN 1 .. PRODLINE.LAST
     LOOP
          OPEN RECOMMENDATION FOR
               SELECT    CAST (REKOM_ID_SEQ.NEXTVAL AS VARCHAR (10) )
                      || '_'
                      || CP.ID_REKOM_OFERTA
                      || '_'
                      || TO_CHAR (SYSDATE, 'yyyymmdd') AS RECOMMENDATIONID,
                      CP.ID_REKOM_OFERTA AS OFFERINGID,
                      CP.PRIORYTET AS PRIORITY
                 FROM REKOM_CROSS_PROM CP
                WHERE CP.LINIA_PROD = PRODLINE (I)
                      AND PRODPRICE (I) BETWEEN CP.CENA_MIN AND CP.CENA_MAX;
     END LOOP;
END GETOFFERINGRECOMMENDATION;

ИЛИ согласно @krokodilko.. Вы можете сделать, как показано ниже:

CREATE OR REPLACE PROCEDURE GETOFFERINGRECOMMENDATION (
     CARTLINELIST     IN       CARTLINETYPE,
     RECOMMENDATION   OUT      SYS_REFCURSOR)
IS
     PRODLINE                      VARCHAR2 (20);
     PRODPRICE                     NUMBER (5, 0);
BEGIN
     FOR I IN 1 .. CARTLINELIST.LAST
     LOOP
          --Assign the values of the collection to the variable declared.
          PRODUCTLINE := CARTLINELIST (I).PRODUCTLINE;
          MINIMALPRICE := CARTLINELIST (I).MINIMALPRICE;

          OPEN RECOMMENDATION FOR
               SELECT    CAST (REKOM_ID_SEQ.NEXTVAL AS VARCHAR (10) )
                      || '_'
                      || CP.ID_REKOM_OFERTA
                      || '_'
                      || TO_CHAR (SYSDATE, 'yyyymmdd') AS RECOMMENDATIONID,
                      CP.ID_REKOM_OFERTA AS OFFERINGID,
                      CP.PRIORYTET AS PRIORITY
                 FROM REKOM_CROSS_PROM CP
                WHERE CP.LINIA_PROD = PRODLINE
                      AND PRODPRICE BETWEEN CP.CENA_MIN AND CP.CENA_MAX;
     END LOOP;
END GETOFFERINGRECOMMENDATION;

Демонстрация:

SQL> CREATE OR REPLACE TYPE CARTLINE AS OBJECT (
  2       PRODUCTLINE                   VARCHAR2 (50),
  3       MINIMALPRICE                  DECIMAL
  4  );
  5  /

Type created.

SQL> CREATE OR REPLACE TYPE CARTLINETYPE IS TABLE OF CARTLINE;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE GETOFFERINGRECOMMENDATION (
  2       CARTLINELIST   IN   CARTLINETYPE)
  3  IS
  4       TYPE V_PRODLINE IS TABLE OF VARCHAR2 (20)
  5            INDEX BY PLS_INTEGER;
  6  
  7       TYPE V_PRODPRICE IS TABLE OF NUMBER (5, 0)
  8            INDEX BY PLS_INTEGER;
  9  
 10       PRODLINE                      V_PRODLINE;
 11       PRODPRICE                     V_PRODPRICE;
 12  BEGIN
 13       SELECT PRODUCTLINE,
 14              MINIMALPRICE
 15       BULK COLLECT INTO PRODLINE,
 16               PRODPRICE
 17         FROM TABLE (CARTLINELIST);
 18  
 19       FOR I IN 1 .. PRODLINE.COUNT
 20       LOOP
 21            DBMS_OUTPUT.PUT_LINE (   'Prod Line '
 22                                  || PRODLINE (I)
 23                                  || '  Prod Price '
 24                                  || PRODPRICE (I) );
 25       END LOOP;
 26  END GETOFFERINGRECOMMENDATION;
 27  /

Procedure created.

Выход:

SQL> DECLARE
  2       VAR                           CARTLINETYPE := CARTLINETYPE ();
  3  BEGIN
  4       --Popuating the collection
  5       VAR.EXTEND (2);
  6       VAR (1) := CARTLINE ('TypeA', 6.0);
  7       VAR (2) := CARTLINE ('TypeB', 7.1);
  8    
  9    --Calling the procedure
 10       GETOFFERINGRECOMMENDATION (CARTLINELIST        => VAR);
 11  END;
 12  /
Prod Line TypeA  Prod Price 6
Prod Line TypeB  Prod Price 7

PL/SQL procedure successfully completed.

SQL> 

Используйте простые присваивания вместо SELECT ... FROM TABLE(cartLineList(i));:

LOOP
/* SELECT productLine, minimalPrice INTO prodLine, prodPrice FROM TABLE(cartLineList(i)); */
   productLine := cartLineList(i).productLine;
   minimalPrice := cartLineList(i).minimalPrice;
   .....
   .....
END LOOP;