How to get n element from nested table pl/sql?

Question

I have problem with the compilation of my stored procedure.

create or replace type CartLine as object (
    offeringId  OfferingIdList
    ,productLine      varchar2(50)
    ,equipment        char(1)
    ,installment       CHAR(1)
    ,cartItemProcess             varchar2(50)
    ,minimalPrice    decimal
);

create or replace type CartLineType is table of CartLine;

create or replace PROCEDURE GetOfferingRecommendation (
    cartLineList IN CartLineType,
    user IN UserType, 
    customer IN CustomerType, 
    processContext IN ProcessContextType, 
    recommendation out SYS_REFCURSOR  )
IS
    prodLine VARCHAR2(20);
    prodPrice NUMBER(5,0);
BEGIN
    FOR i IN cartLineList.FIRST .. cartLineList.LAST
    LOOP
        SELECT productLine, minimalPrice 
        INTO prodLine, prodPrice  
        FROM TABLE(cartLineList(i));
        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;

It is not getting compiled cause the following statement is wrong:

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

I want to select only single value every all new iteration of my loop. Can somebody help me to resolve my problem?

-- EDIT 1/9/2018 4:26 PM According to topic: How to return result of many select statements as one custom table I tried to rebuild my procedure. I created types for test:

create or replace TYPE tst AS OBJECT (
rekom_id varchar2(50)
,rekom_priorytet number(5,4)
);
/
create or replace TYPE tst_list IS TABLE OF tst;

After that, I changed my procedure like below:

CREATE OR REPLACE PROCEDURE GetOfferingRecommendation (cartLineList IN CartLineType, recommendation out SYS_REFCURSOR  )
IS
CURSOR CUR_TAB IS SELECT productLine, minimalPrice FROM TABLE(cartLineList);
v_tst tst_list;
BEGIN
FOR i IN CUR_TAB
LOOP
    EXECUTE IMMEDIATE 'SELECT tst_list(
         CAST(REKOM_ID_SEQ.NEXTVAL AS VARCHAR(10))||''_''||cp.ID_REKOM_OFERTA||''_''||TO_CHAR(SYSDATE, ''yyyymmdd'')
        ,cp.PRIORYTET)
        FROM REKOM_CROSS_PROM cp
        WHERE cp.LINIA_PROD ='||i.productLine||' AND '||i.minimalPrice||' BETWEEN cp.CENA_MIN AND cp.CENA_MAX'
        BULK COLLECT INTO v_tst;
    EXIT WHEN CUR_TAB%NOTFOUND;
    FOR REC IN 1 .. v_tst.COUNT
    LOOP
    PIPE ROW (v_tst(REC));
    END LOOP;
END LOOP;
OPEN recommendation FOR SELECT * FROM TABLE(v_tst);
END IF;
END GetOfferingRecommendation;

But I can't compile because error occured: PLS-00629 Would you please told me what I do wrong?

sounds like you want to do `where cp.linia_prod = cartlinlist(i).productline` instead of trying to select from the array into the prodline variable and using that in the ref cursor? However, it doesn't make sense for you to create open a ref cursor for every element in the array; perhaps you mean to do something like `where cp.linia_prod = (select productline from table(cartlinelist)`? — Boneist, Jan 08 '18 at 16:00
I don't understand. Are you showing only part of your procedure? If this is your entire procedure, what is it supposed to do? It selects some things and assigns them to variables, and then it doesn't do anything at all with those variables. What's the point? — , Jan 08 '18 at 16:10
You need to explain the business logic you are trying to implement. We can't reverse engineer your intentions from some shonky code. For a start, the OUT parameter is a scalar, so the procedure will only return the last instance of the ref cursor opened in the loop. Presumably that's not what you want to ahppen. — APC, Jan 09 '18 at 08:21
For example I will receive cartLineList nested table with 2 elements. First element have productLine = 'prod1' and minimalPrice = 10 Second element: productLine = 'prod2' and minimalPrice = 20 I want to return cursor with 2 records from table REKOM_CROSS_PROM where values are equal for elements from cartLineList parameter. Ok I know that I can use following statement: **where cp.linia_prod = (select productline from table(cartlinelist)** But how to equal multiple minimalPrice in BETWEEN clause? — anton1009, Jan 09 '18 at 09:52

score 0 · Answer 1 · answered Jan 08 '18 at 17:10

Use simple assignments instead of 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;

score 0 · Accepted Answer · answered Jan 09 '18 at 09:54

You cannot assign variables using a select statement from a collection in a loop like below.

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

The collection elements cannot be referred inside a SELECT statement 1 by 1 using a loop. You can loop through the collection as

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

Collection has a number of rows and when you do so, you try to assign many rows to a single variable, which is wrong. You can do either of the below explained. There relevant explanation is inline.

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;

OR as per @krokodilko.. You can do as below:

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;

Demo:

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.

Output:

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>

Unfortunately, it's not resolving my problem. Like @APC wrote, cursor will return only last instance of cursor opened in loop, so It will be only one record, not 2 :/ — anton1009, Jan 09 '18 at 10:21
@anton1009 Which cursor. I showed you in example, 2 records are returned. Did you exactly follow what i showed. — XING, Jan 09 '18 at 10:25
I understand your example, but when I change DBMS_OUTPUT.PUT_LINE method for OPEN CURSOR FOR SELECT clause (like krokodilko example), the cursor return only one one record. I need use all nested table elements in SELECT clause to return cursor with multiple records from REKOM_CROSS_PROM table. — anton1009, Jan 09 '18 at 10:36
@anton1009. For your case i already answered such type of question. You might check this. This uses piperow to get data of collection. https://stackoverflow.com/questions/48141105/how-to-return-result-of-many-select-statements-as-one-custom-table/48146098#48146098 — XING, Jan 09 '18 at 10:40
will it be work with SYS_REFCURSOR or only with PIPELINED TABLE? — anton1009, Jan 09 '18 at 10:58
@anton1009 You can create an `OUT` parameter. Else you can first populate the collection using piperow and then open `SYS_REFCURSOR` for collection — XING, Jan 09 '18 at 11:06
A webservice which will be call the procedure expect SYS_REFCURSOR in response, so I will try build solution using second option. Thanks for your answers. — anton1009, Jan 09 '18 at 11:28
I edited my first post. Would you please to have a look at this? I tried to implement your second idea, but error occur :/ — anton1009, Jan 09 '18 at 15:30
@anton1009 You didnot follow my link https://stackoverflow.com/questions/48141105/how-to-return-result-of-many-select-statements-as-one-custom-table/48146098#48146098 If you see am creating a pipelined function not a procedure. Pipelining works with Function not with procedure. You red my link step by step and am sure you would be able to implement, — XING, Jan 10 '18 at 02:42

How to get n element from nested table pl/sql?

2 Answers2