Can you use a variable as a name for Nested Table or an item in a Collections?
I'm trying to find a way to use a variable so I can use a loop.
This environment do not have 'create' permission in so that limits some of my choices.
I've included the error message in the code sample below with the line that errors marked.
Oracle Database 10g
PL/SQL 8.0.0.1480
Code:
DECLARE
-- Declare the nested table, its data type and max number of characters
TYPE MyNestedTable IS TABLE of varchar2(100);
-- Declare the 'columns' in the nested table
MyCol_1 MyNestedTable;
MyCol_2 MyNestedTable;
v_MyCol_n VARCHAR2(200);
v_LoopCounter number;
BEGIN
-- Initialize the 'columns' that are in the nested table and set their starting value to null
MyCol_1 := MyNestedTable();
MyCol_2 := MyNestedTable();
--************
--* Instead of this 'hard coded' value I would like a variable for use with a loop
--************
MyCol_1.EXTEND;
MyCol_1.EXTEND;
MyCol_1(1) := 'abc';
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));
--************
--* This 'variable' method with the loop counter does not work
--************
v_LoopCounter:=1;
v_MyCol_n := 'MyCol_' ||v_LoopCounter;
execute Immediate (v_MyCol_n);
v_MyCol_n := .EXTEND; --*** <-- ERRORS ON THIS LINE ***
v_MyCol_n (v_LoopCounter) := 'abc';
--For Testing
DBMS_OUTPUT.PUT_LINE('v_MyCol_n '||v_MyCol_n (v_LoopCounter));
--************
--* The Error message received on the above code is:
--* ORA-06550: line 34, column 22:
--* PLS-00103: Encountered the symbol “.” When expecting on of the following:…
--************
MyCol_2.EXTEND;
MyCol_2(1) := 200;
--For Testing
--DBMS_OUTPUT.PUT_LINE('MyCol_2 '||MyCol_2(1));
-- Review results
DBMS_OUTPUT.PUT_LINE('MyCol_1 '|| ', '||'MyCol_2 ');
DBMS_OUTPUT.PUT_LINE(MyCol_1(1)|| ', '||MyCol_2(1));
END;
Thanks for any help.
Tom