how to create dynamic variable name inside a for loop in sql

Question

I am new to sql. not an expert. I have a proc where i need to extract some values from two tables using a key tables ord_item and product_ext_data view_id is the key.

i want to extract the param_value from the product_ext_data into variables d_DestNumber1,d_DestNumber2 ...d_DestNumber10(max 10 values expected)

For this i am using the for loop to iterate through the cursor output and assign each param_value to each variable. I am not sure how to create the variable name dynamically based on the i value in the for loop

               CREATE OR REPLACE PROCEDURE C1_REMOVEFNFDATA(v_soid                  VARCHAR2,
                    C1_REMOVEFNFDATA_cv IN OUT cv_types.customer_tp) IS

   l_count NUMBER := 0;
 d_DestNumber1 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber2 PRODUCT_EXT_DATA.param_value%TYPE;
  d_DestNumber3 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber4 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber5 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber6 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber7 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber8 PRODUCT_EXT_DATA.param_value%TYPE;
 d_DestNumber9 PRODUCT_EXT_DATA.param_value%TYPE;
d_DestNumber10 PRODUCT_EXT_DATA.param_value%TYPE;

    CURSOR c_dest_num IS
   SELECT P.VIEW_ID as view_id, P.param_value as destination_number
    FROM ORD_ITEM o, 
     PRODUCT_EXT_DATA p
    WHERE o.service_order_id = to_number(v_soid)
  AND O.ITEM_ACTION_ID IN (30) -- delete
  AND P.VIEW_ID          = O.VIEW_ID
  AND P.PARAM_ID         = 5100
  AND o.is_cancelled     = 0;
  d_dest_num c_dest_num%rowtype;

  BEGIN
   SELECT count(*) 
  INTO l_count
    FROM ORD_ITEM o, 
     PRODUCT_EXT_DATA p
  WHERE o.service_order_id = to_number(v_soid)
 AND o.member_type      = 10   -- product
 AND O.ITEM_ACTION_ID IN (30) -- delete
 AND P.VIEW_ID          = O.VIEW_ID
 AND P.PARAM_ID         = 5100
 AND o.is_cancelled     = 0;

    IF(l_count != 0) THEN

     OPEN c_dest_num;
   LOOP FETCH c_dest_num INTO d_dest_num;
   EXIT WHEN c_dest_num%NOTFOUND;

    for i in 1 .. l_count
   LOOP
   d_DestNumber+i := d_dest_num.destination_number;

   END LOOP;
   END LOOP;
   CLOSE c_dest_num;
   END IF;



  OPEN C1_REMOVEFNFDATA_CV FOR 
   SELECT l_count AS FnfRemoveCompCount,
    d_DestNumber1 AS DestNumber1,
    d_DestNumber2 AS DestNumber2,
    d_DestNumber3 AS DestNumber3,
    d_DestNumber4 AS DestNumber4,
    d_DestNumber5 AS DestNumber5,
    d_DestNumber6 AS DestNumber6,
    d_DestNumber7 AS DestNumber7,
    d_DestNumber8 AS DestNumber8,
    d_DestNumber9 AS DestNumber9,
    d_DestNumber10 AS DestNumber10,
    FROM DUAL;

   END;

Answer 1

You can't have a dynamic variable name. But you could use a collection (as sblandin showed; this is using an associative array rather than a varray but same idea):

CREATE OR REPLACE PROCEDURE C1_REMOVEFNFDATA(v_soid VARCHAR2,
  C1_REMOVEFNFDATA_cv IN OUT cv_types.customer_tp)
IS
  l_count NUMBER := 0;
  TYPE t_destnumbers IS TABLE OF PRODUCT_EXT_DATA.param_value%TYPE
    INDEX BY PLS_INTEGER;
  d_destnumber t_destnumbers;

  CURSOR c_dest_num IS
    SELECT P.VIEW_ID as view_id, P.param_value as destination_number
    FROM ORD_ITEM o
    JOIN PRODUCT_EXT_DATA p
    ON P.VIEW_ID = O.VIEW_ID
    WHERE o.service_order_id = to_number(v_soid)
    AND O.ITEM_ACTION_ID IN (30) -- delete
    AND P.PARAM_ID = 5100
    AND o.is_cancelled = 0;

  d_dest_num c_dest_num%rowtype;

BEGIN
  SELECT count(*) 
  INTO l_count
  FROM ORD_ITEM o
  JOIN PRODUCT_EXT_DATA p
  ON P.VIEW_ID = O.VIEW_ID
  WHERE o.service_order_id = to_number(v_soid)
  AND o.member_type = 10 -- product
  AND O.ITEM_ACTION_ID IN (30) -- delete
  AND P.PARAM_ID = 5100
  AND o.is_cancelled = 0;

  IF(l_count != 0) THEN
     OPEN c_dest_num;
     LOOP
       FETCH c_dest_num INTO d_dest_num;
       EXIT WHEN c_dest_num%NOTFOUND;
       FOR i IN 1 .. l_count
       LOOP
         d_DestNumber(i) := d_dest_num.destination_number;
       END LOOP;
    END LOOP;
    CLOSE c_dest_num;
  END IF;

  -- populate the required nuimber of entries with null
  FOR i IN l_count + 1 .. 10 LOOP
    d_DestNumber(i) := null;
  END LOOP;

  OPEN C1_REMOVEFNFDATA_CV FOR 
    SELECT l_count AS FnfRemoveCompCount,
    d_DestNumber(1) AS DestNumber1,
    d_DestNumber(2) AS DestNumber2,
    d_DestNumber(3) AS DestNumber3,
    d_DestNumber(4) AS DestNumber4,
    d_DestNumber(5) AS DestNumber5,
    d_DestNumber(6) AS DestNumber6,
    d_DestNumber(7) AS DestNumber7,
    d_DestNumber(8) AS DestNumber8,
    d_DestNumber(9) AS DestNumber9,
    d_DestNumber(10) AS DestNumber10
    FROM DUAL;
END;
/

I don't think it's dong quite what you want though; the last parameter value seen is copied to each of the l_count variables, so if you actually have three values - say P1, P3, P2 and it happens to retrieve them in that order - then you see:

FNFREMOVECOMPCOUNT                      DESTNUMBER1                      DESTNUMBER2                      DESTNUMBER3                      DESTNUMBER4                      DESTNUMBER5                      DESTNUMBER6                      DESTNUMBER7                      DESTNUMBER8                      DESTNUMBER9                      DESTNUMBER10                     
--------------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- 
3                                       P2                               P2                               P2                                                                                                                                                                                                                                                                      

You probably want to increment l_count inside the cursor loop. This uses a simpler construct, and does away with the initial counting query:

CREATE OR REPLACE PROCEDURE C1_REMOVEFNFDATA(v_soid VARCHAR2,
  C1_REMOVEFNFDATA_cv IN OUT cv_types.customer_tp)
IS
  l_count NUMBER := 0;
  TYPE t_destnumbers IS TABLE OF PRODUCT_EXT_DATA.param_value%TYPE
    INDEX BY PLS_INTEGER;
  d_destnumber t_destnumbers;
BEGIN
  FOR d_dest_num IN (
    SELECT P.VIEW_ID as view_id, P.param_value as destination_number
    FROM ORD_ITEM o
    JOIN PRODUCT_EXT_DATA p
    ON P.VIEW_ID = O.VIEW_ID
    WHERE o.service_order_id = to_number(v_soid)
    AND o.member_type = 10 -- product
    AND O.ITEM_ACTION_ID = 30 -- delete
    AND P.PARAM_ID = 5100
    AND o.is_cancelled = 0
  )
  LOOP
    l_count := l_count + 1;
    d_DestNumber(l_count) := d_dest_num.destination_number;
  END LOOP;

  -- populate the required nuimber of entries with null
  FOR i IN l_count + 1 .. 10 LOOP
    d_DestNumber(i) := null;
  END LOOP;

  OPEN C1_REMOVEFNFDATA_CV FOR 
    SELECT l_count AS FnfRemoveCompCount,
    d_DestNumber(1) AS DestNumber1,
    d_DestNumber(2) AS DestNumber2,
    d_DestNumber(3) AS DestNumber3,
    d_DestNumber(4) AS DestNumber4,
    d_DestNumber(5) AS DestNumber5,
    d_DestNumber(6) AS DestNumber6,
    d_DestNumber(7) AS DestNumber7,
    d_DestNumber(8) AS DestNumber8,
    d_DestNumber(9) AS DestNumber9,
    d_DestNumber(10) AS DestNumber10
    FROM DUAL;
END;
/

And with the same data this gets:

FNFREMOVECOMPCOUNT                      DESTNUMBER1                      DESTNUMBER2                      DESTNUMBER3                      DESTNUMBER4                      DESTNUMBER5                      DESTNUMBER6                      DESTNUMBER7                      DESTNUMBER8                      DESTNUMBER9                      DESTNUMBER10                     
--------------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- -------------------------------- 
3                                       P1                               P3                               P2                                                                                                                                                                                                                                                                      

You don't need a procedure or cursor loop for this though, if I understand what it's doing properly; you can use plain SQL and a pivot:

SELECT * FROM (
  SELECT COUNT(*) OVER (PARTITION BY P.VIEW_ID) as FNFREMOVECOMPCOUNT,
    P.param_value as destination_number,
    ROW_NUMBER() OVER (PARTITION BY P.VIEW_ID ORDER BY null) AS rn
  FROM ORD_ITEM o
  JOIN PRODUCT_EXT_DATA p
  ON P.VIEW_ID = O.VIEW_ID
  WHERE o.service_order_id = to_number(:v_soid)
  AND o.member_type = 10 -- product
  AND O.ITEM_ACTION_ID = 30 -- delete
  AND P.PARAM_ID = 5100
  AND o.is_cancelled = 0
)
PIVOT (MAX(destination_number) AS destnumber
  FOR (rn) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10));

This adds a rn column based on the row number, which will be 1 to 10 (at most), and that is then used for the pivot IN clause. And it adds an analytic count to get the equivalent of the procedure's l_count. That gets the same result:

FNFREMOVECOMPCOUNT 1_DESTNUMBER 2_DESTNUMBER 3_DESTNUMBER 4_DESTNUMBER 5_DESTNUMBER 6_DESTNUMBER 7_DESTNUMBER 8_DESTNUMBER 9_DESTNUMBER 10_DESTNUMBE
------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
                 3 P1           P2           P3                                                                                                     

SQL Fiddle

Answer 2

You can make use of an array instead of multiple variables. In your declare section write:

  type DestNumber is varray(10) of PRODUCT_EXT_DATA.param_value%TYPE;
  --Initializes the array
  d_DestNumber DestNumber := DestNumber(0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

Then in your FOR ... LOOP you can use:

d_DestNumber(i) := d_dest_num.destination_number;

I do not fully understand the use of d_DestNumbers in the final cursor however I hope you can make use of my answer ;-)

You can find many useful information about arrays in this question: Oracle PL/SQL - How to create a simple array variable?