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
