title: pl/sql - can collection loop through column names?
body: <p>The output from the below code is:</p>
  
  <pre><code>         |LAT|MISC|SID|NO
  MIN_LENGTH|1|2|1|1
  MAX_LENGTH|6|6|4|2
  </code></pre>
  
  <p>The output is as I  expect, but is there anyway to loop through the columns using an index (ie. j) instead of doing RESULTS(I).<strong>MAX_LENGTH</strong> , RESULTS(I).<strong>MAX_LENGTH</strong> etc ? The concern is that when adding extra columns to the 'R_RESULT_REC' record, another loop is required.</p>
  
  <pre><code>set serveroutput on
  DECLARE     
    TYPE R_RESULT_REC IS RECORD
      (COL_NAME        VARCHAR2(100),
        MIN_LENGTH         NUMBER,
        MAX_LENGTH         NUMBER
        );       
    TYPE tr_RESULT IS TABLE OF R_RESULT_REC;
    RESULTS   TR_RESULT := TR_RESULT();
    v_counter NUMBER := 1;
  BEGIN
    FOR J IN (SELECT DISTINCT COLUMN_NAME FROM ALL_TAB_COLUMNS 
              WHERE OWNER = 'SYSTEM'
              and TABLE_NAME = 'SPECCHAR')
    LOOP
        RESULTS.EXTEND;
        RESULTS(V_COUNTER).COL_NAME := J.COLUMN_NAME;
        EXECUTE IMMEDIATE 'SELECT MIN(LENGTH('||J.COLUMN_NAME||')),
        MAX(LENGTH('||J.COLUMN_NAME||'))
        FROM '||'SYSTEM'||'.'||'SPECCHAR' INTO 
        RESULTS(V_COUNTER).MIN_LENGTH,
        RESULTS(V_COUNTER).MAX_LENGTH; 
        V_COUNTER := V_COUNTER + 1;
    END LOOP;
       FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
         IF I = RESULTS.LAST THEN
           DBMS_OUTPUT.PUT_LINE(RESULTS(I).COL_NAME);
         ELSIF I = RESULTS.FIRST THEN
           DBMS_OUTPUT.PUT('         |'||RESULTS(I).COL_NAME||'|');
          ELSE
           DBMS_OUTPUT.PUT(RESULTS(I).COL_NAME||'|');
         END IF ;
      END LOOP;
      FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
         IF I = RESULTS.LAST THEN
           DBMS_OUTPUT.PUT_LINE(RESULTS(I).MIN_LENGTH);
          ELSIF I = RESULTS.FIRST THEN
           DBMS_OUTPUT.PUT('MIN_LENGTH|'||RESULTS(I).MIN_LENGTH||'|');
          ELSE
           DBMS_OUTPUT.PUT(RESULTS(I).MIN_LENGTH||'|');
         END IF ;
      END LOOP;
       FOR I IN RESULTS.FIRST .. RESULTS.LAST LOOP
         IF I = RESULTS.LAST THEN
           DBMS_OUTPUT.PUT_LINE(RESULTS(I).MAX_LENGTH);
          ELSIF I = RESULTS.FIRST THEN
           DBMS_OUTPUT.PUT('MAX_LENGTH|'||RESULTS(I).MAX_LENGTH||'|');
          ELSE
           DBMS_OUTPUT.PUT(RESULTS(I).MAX_LENGTH||'|');
         END IF ;
      END LOOP;
  end;
  </code></pre>