Can you use a variable as a name for Nested Table or an item in a Collections?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Can you use a variable as a name for Nested Table or an item in a Collections?

Hybrid View

  1. #1
    Join Date
    Sep 2012
    Posts
    3

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    PL/SQL does not know where to find .extend, it is a method on an object not a stand alone function.

    v_MyCol_n := .EXTEND;
    From looking at this it isn't very clear where your data is coming from or what you are doing with it. If you really want to use a PL/SQL collection you need to define a rowtype if it is something other than a table, then you define a collection, then you select col1, col2, etc bulk collect into Then you can iterate through the collection and do what ever it is that you are trying to do. This isn't Java, you can't define a with block and have it implied that you are referencing a particular object.

    IMHO, you need to explain what you are ultimately trying to accomplish.
    this space intentionally left blank

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    @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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    I'm still not sure what you are trying to get, but I suspect that you can get
    what you want with dynamic SQL based on the dba_tab_columns table.
    It just seems like you are making this way too complex.

    Code:
    SQL> select 'mycol_'||rownum||'_'||column_name
      2  from dba_tab_columns
      3  where owner='DBSNMP' and table_name='BSLN_STATISTICS'
      4  order by column_id;
    
    'MYCOL_'||ROWNUM||'_'||COLUMN_NAME
    ----------------------------------------------------------
    mycol_1_BSLN_GUID
    mycol_2_METRIC_ID
    mycol_3_COMPUTE_DATE
    mycol_4_TIMEGROUPING
    mycol_5_TIMEGROUP
    mycol_6_SAMPLE_COUNT
    mycol_7_AVERAGE
    mycol_8_MINIMUM
    mycol_9_MAXIMUM
    mycol_10_SDEV
    mycol_11_PCTILE_25
    mycol_12_PCTILE_50
    mycol_13_PCTILE_75
    mycol_14_PCTILE_90
    mycol_15_PCTILE_95
    mycol_16_PCTILE_99
    mycol_17_EST_SAMPLE_COUNT
    mycol_18_EST_SLOPE
    mycol_19_EST_INTERCEPT
    mycol_20_EST_FIT_QUALITY
    mycol_21_EST_PCTILE_999
    mycol_22_EST_PCTILE_9999
    
    22 rows selected.
    
    Elapsed: 00:00:00.12
    this space intentionally left blank

  5. #5
    Join Date
    Sep 2012
    Posts
    3
    @gandolf989 – Thanks for the help and insight.

    I’ll rework the logic and code to try and find someway through this.

    Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width