oracle - ORA-01007 "variable not in select list"来自 dbms_sql.column_value 调用

Question

我正在尝试使用动态 SQL 对模式中的所有数据进行采样:

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

当我运行该命令时，它在 dbms_sql.column_value 调用行中给出了此错误:

ORA-01007: variable not in select list

如果我注释掉 dbms_sql.column_value 调用它仍然是错误，但现在:

ORA-01002: fetch out of sequence

我做错了什么？

Answer 1

您发布的代码中有两个问题。首先，您跳过了 the execution flow 的一部分因为你还没有打电话the DEFINE_COLUMN procedure 。这就是导致 ORA-01007 错误的原因，因为尚未通过该调用告知动态 SQL 处理有关选择列表列的信息。对于您当前的代码，您只需要定义第 2 列，但假设您实际上想要引用其他列，您可以在循环中定义它们。要将它们全部视为字符串进行显示，您可以执行以下操作:

...
  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
    ...

如果您想做任何需要将变量视为正确类型的事情，您可以拥有每种类型的局部变量并使用 rec_tab 中的数据类型您已经从 describe_columns 获得的信息为每列使用适当类型的变量。

第二个问题，您在评论 column_value 时遇到的问题电话，一旦定义问题得到解决，仍然存在。您的循环永远不会退出，因此在从游标中获取最后一行后，您会进行进一步的无效获取，这会引发 ORA-01002。您已经有了避免这种情况的代码，但它已被注释掉:

...
  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; 
...

通过这两项更改，您的代码将运行，并转储 View 结构:

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
...