@gandolf989

Thanks for taking a look at this.

In this beta code I’m trying to work out how to use a concatenated variable with the ‘root base’ of a field name and then take an action on that concatenated value.

The full code for the project is not shown here since building the concatenation and then taking an action on that concatenation is the part I’m stuck on.

What this sample beta code does is building the nested table, add some ‘fields’ to it and then populating those fields with values hard coded in this sample code.

By way of some back ground, this ‘hard coded’ part works:

Code:
        --************
        --* Instead of this 'hard coded' value for a field name I would like a variable for use with a loop
        --* IMPORTANT:
        --* This method below works and is included as an example of what I'd like
        --* to change to a variable, i.e. the column name concatenated to a number  
        --************
        MyCol_1.EXTEND;        
        MyCol_1(1) := 'abc';
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));
I’ve tried (many) different version of this code, but still don’t have it working yet.

To address the issue that you’ve pointed out about ‘.extend’ I been trying to use the EXEC() and/or EXECUTE() as in this sample, but I don't have it worked out yet. Any thoughts on how to fix this?

Code:
        --************
        --* This new 'variable' method with the loop counter concatenated to 'MyCol_' does not work
        --************
        v_LoopCounter:=1;
        
        v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
        EXEC(v_MyTestCode) ;

        v_SomeValue := 'abc;';
        v_MyTestCode := 'MyCol_' || v_LoopCounter || ':= '||v_SomeValue;
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
        EXEC(v_MyTestCode) ;
Here is the full code since I’ve added some new variables to try and get this to work.

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;
       
       v_MyTestCode   VARCHAR2(200);
       v_SomeValue    VARCHAR2(200);

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
        --* IMPORTANT:
        --* This method below works and is included as an example of what I'd like
        --* to change to a variable, i.e. the column name concatenated to a number  
        --************
        MyCol_1.EXTEND;        
        MyCol_1(1) := 'abc';
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('MyCol_1 '||MyCol_1(1));


        --************
        --* This new 'variable' method with the loop counter concatenated to 'MyCol_' does not work
        --************
        v_LoopCounter:=1;
        
        v_MyTestCode := 'MyCol_' || v_LoopCounter || '.EXTEND' ;
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
        EXEC(v_MyTestCode) ;

        v_SomeValue := 'abc;';
        v_MyTestCode := 'MyCol_' || v_LoopCounter || ':= '||v_SomeValue;
        --For Testing
        --DBMS_OUTPUT.PUT_LINE('v_MyTestCode '||v_MyTestCode);
        EXEC(v_MyTestCode) ;


        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 again for taking a look at this.
Tom