使用dbms_sql.native时，能否从对象类型表变量中查询数据？

错误原因 #

SQL执行引擎无法直接识别PL/SQL块内的局部变量。你用dbms_sql.native执行的select count(*) cou from table(tRows)语句运行在SQL上下文，而tRows是PL/SQL存储过程的局部变量，默认不在SQL的可见范围内，因此触发ORA-00904错误。

解决方案 #

方案1：用EXECUTE IMMEDIATE配合绑定变量（更简洁） #

如果不需要依赖dbms_sql的特定功能，直接用EXECUTE IMMEDIATE绑定PL/SQL变量即可完成查询：

-- 先确认已定义模式级对象类型和表类型
CREATE OR REPLACE TYPE SI_O AS OBJECT (
  id NUMBER,
  name VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE SI_T AS TABLE OF SI_O;
/

CREATE OR REPLACE PROCEDURE NATIVE_TEST IS
  tRows SI_T;
  v_count NUMBER;
BEGIN
  -- 批量收集数据到tRows
  EXECUTE IMMEDIATE 'SELECT SI_O(id, name) FROM your_source_table' 
    BULK COLLECT INTO tRows;

  -- 通过绑定变量传递tRows到SQL语句
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM TABLE(:p_rows)' 
    INTO v_count 
    USING tRows;

  DBMS_OUTPUT.PUT_LINE('记录总数：' || v_count);
END;
/

方案2：使用dbms_sql绑定变量（必须用dbms_sql时） #

如果业务场景必须依赖dbms_sql，需要手动将PL/SQL变量绑定到SQL执行上下文：

CREATE OR REPLACE PROCEDURE NATIVE_TEST IS
  tRows SI_T;
  v_count NUMBER;
  v_cursor INTEGER;
  v_rows_processed INTEGER;
BEGIN
  -- 批量收集数据
  EXECUTE IMMEDIATE 'SELECT SI_O(id, name) FROM your_source_table' 
    BULK COLLECT INTO tRows;

  -- 初始化dbms_sql游标
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    -- 解析带绑定变量的SQL语句
    DBMS_SQL.PARSE(
      c => v_cursor,
      statement => 'SELECT COUNT(*) FROM TABLE(:p_rows)',
      language_flag => DBMS_SQL.NATIVE
    );

    -- 将PL/SQL变量tRows绑定到占位符:p_rows
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':p_rows', tRows);

    -- 定义输出列的类型
    DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count);

    -- 执行SQL语句
    v_rows_processed := DBMS_SQL.EXECUTE(v_cursor);

    -- 获取查询结果
    IF DBMS_SQL.FETCH_ROWS(v_cursor) > 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_count);
      DBMS_OUTPUT.PUT_LINE('记录总数：' || v_count);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
      RAISE;
  END;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

关键注意点 #

• 确保SI_O和SI_T是模式级对象（通过CREATE TYPE定义，而非PL/SQL块内的局部类型），否则SQL层的TABLE()函数无法识别该类型。
• 绑定变量是核心：通过:p_rows这类占位符，将PL/SQL变量传入SQL执行上下文，让SQL引擎能正确识别tRows。

内容的提问来源于stack exchange，提问作者hajduk