title: ORA-01007 "variable not in select list" from dbms_sql.column_value call
body: <p>I am trying to use dynamic SQL to sample all the data in a schema with a pattern:</p>
  
  <pre><code>DECLARE
    xsql varchar2(5000);
  
  
    c           NUMBER;
    d           NUMBER;
    col_cnt     INTEGER;
    f           BOOLEAN;
    rec_tab     DBMS_SQL.DESC_TAB;
    col_num    NUMBER;
    varvar    varchar2(500);
  
    PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
    BEGIN
      DBMS_OUTPUT.ENABLE(1000000); 
      DBMS_OUTPUT.NEW_LINE;
      DBMS_OUTPUT.PUT_LINE('col_type            =    '
                           || rec.col_type);
      DBMS_OUTPUT.PUT_LINE('col_maxlen          =    '
                           || rec.col_max_len);
      DBMS_OUTPUT.PUT_LINE('col_name            =    '
                           || rec.col_name);
      DBMS_OUTPUT.PUT_LINE('col_name_len        =    '
                           || rec.col_name_len);
      DBMS_OUTPUT.PUT_LINE('col_schema_name     =    '
                           || rec.col_schema_name);
      DBMS_OUTPUT.PUT_LINE('col_schema_name_len =    '
                           || rec.col_schema_name_len);
      DBMS_OUTPUT.PUT_LINE('col_precision       =    '
                           || rec.col_precision);
      DBMS_OUTPUT.PUT_LINE('col_scale           =    '
                           || rec.col_scale);
      DBMS_OUTPUT.PUT('col_null_ok         =    ');
      IF (rec.col_null_ok) THEN
        DBMS_OUTPUT.PUT_LINE('true');
      ELSE
        DBMS_OUTPUT.PUT_LINE('false');
      END IF;
    END;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
  
      xsql:='  
  WITH     got_r_num    AS
  (
       SELECT  e.*     -- or whatever columns you want
       ,     ROW_NUMBER () OVER (ORDER BY  dbms_random.value)     AS r_num
       FROM     dba_tab_columns  e
  )
  SELECT  *     -- or list all columns except r_num
  FROM     got_r_num
  WHERE     r_num     &lt;= 10';
  
  
    DBMS_SQL.PARSE(c, xsql, DBMS_SQL.NATIVE);
  
    d := DBMS_SQL.EXECUTE(c);
  
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  
       LOOP 
         IF DBMS_SQL.FETCH_ROWS(c)&gt;0 THEN 
           NULL;
           -- get column values of the row 
           DBMS_SQL.COLUMN_VALUE(c, 2, varvar); 
           --dbms_output.put_line('varvar=');
           --DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
           --DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var); 
  
    -- Bind the row into the cursor that inserts into the destination table. You 
    -- could alter this example to require the use of dynamic SQL by inserting an 
    -- if condition before the bind. 
          --DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var); 
          --DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var); 
          --DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind', 
  --birthdate_var); 
          --ignore := DBMS_SQL.EXECUTE(destination_cursor); 
        --ELSE 
  
    -- No more rows to copy: 
          --EXIT; 
        END IF; 
      END LOOP; 
  
  
  
      --EXIT WHEN d != 10;
    --END LOOP;
  
  
    col_num := rec_tab.first;
    IF (col_num IS NOT NULL) THEN
      LOOP
        print_rec(rec_tab(col_num));
        col_num := rec_tab.next(col_num);
        EXIT WHEN (col_num IS NULL);
      END LOOP;
    END IF;
  
    DBMS_SQL.CLOSE_CURSOR(c);
  END;
  /
  </code></pre>
  
  <p>When I run that it gives me this error from the line with the <code>dbms_sql.column_value</code> call:</p>
  
  <pre><code>ORA-01007: variable not in select list
  </code></pre>
  
  <p>If I comment out that <code>dbms_sql.column_value</code> call it still errors but now with: </p>
  
  <pre><code>ORA-01002: fetch out of sequence
  </code></pre>
  
  <p>What am I doing wrong?</p>