ORA-01007 "variable not in select list" from dbms_sql.column_value call

Question

I am trying to use dynamic SQL to sample all the data in a schema with a pattern:

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     <= 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)>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;
/

When I run that it gives me this error from the line with the dbms_sql.column_value call:

ORA-01007: variable not in select list

If I comment out that dbms_sql.column_value call it still errors but now with:

ORA-01002: fetch out of sequence

What am I doing wrong?

Answer 1

You have two problems in the code you posted. Firstly you have skipped part of the execution flow because you haven't called the DEFINE_COLUMN procedure. That is what is causing the ORA-01007 error, as the dynamic SQL processing hasn't been told about the select list columns via that call. For your current code you only need to define column 2, but assuming you will actually want to refer to the others you can define them in a loop. To treat them all as string for display you could do:

...
  DBMS_SQL.PARSE(c, xsql, DBMS_SQL.NATIVE);

  d := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

  FOR i IN 1..col_cnt
  LOOP
    -- dbms_output.put_line('col_name is ' || rec_tab(i).col_name);
    DBMS_SQL.DEFINE_COLUMN(c, i, varvar, 500);
  END LOOP;

  LOOP
    IF DBMS_SQL.FETCH_ROWS(c)>0 THEN
    ...

If you want to do anything that needs to treat the variables as the right types you could have a local variable of each type and use the data type from the rec_tab information you already have from describe_columns to use the appropriately typed variable for each column.

The second problem, which you were hitting when you commented the column_value call, is still there once that definbe issue has been fixed. Your loop doesn't ever exit, so after you fetch the last row from the cursor you do a further invalid fetch, which throws ORA-01002. You have the code to avoid that already but it's commented out:

...
  LOOP 
    IF DBMS_SQL.FETCH_ROWS(c)>0 THEN 
      -- get column values of the row 
      DBMS_SQL.COLUMN_VALUE(c, 2, varvar); 
      ...
    ELSE 
      -- No more rows to copy: 
      EXIT; 
    END IF; 
  END LOOP; 
...

With those two changes your code runs, and dumps the view structure:

PL/SQL procedure successfully completed.

col_type            =    1
col_maxlen          =    30
col_name            =    OWNER
col_name_len        =    5
col_schema_name     =    
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    false

col_type            =    1
col_maxlen          =    30
col_name            =    TABLE_NAME
...

Answer 2

To those who find this question when accessing Oracle through ODP.NET, as I did:

We started getting this error whenever we would add column to an existing table in our application. I'm not sure what all the conditions were to make it fail, but ours were:

1. Run a SELECT * FROM "table".
2. Include a ROWNUM restriction in the WHERE clause (WHERE ROWNUM < 10).
3. Run that through the ODP.NET dataReader.GetSchemaTable() call.

Running unrestricted queries or running queries directly on Oracle SQL Developer did not seem to cause the error.

I've hit some pretty weird stuff in the past with Oracle connection pooling, so I eventually thought that could be the problem. The solution was to restart the web service to force all the connections to be fully dropped and recreated.

The theory is that the ODP.NET connection from the connection pool still had no idea the column existed on the table, but the column was returned by the database.