运行在 Oracle 的表中存储为值的 sql，并在 SSRS 报告中返回记录集

Question

我有一个查询，它创建一个SQL语句作为字段。我想执行此语句并返回 SSRS 报告中的记录集。

select 'select '||FILE_ID||' FILE_ID,'||
ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
' from RESPONSE_DETAILS where FILE_ID=' ||FILE_ID||';'
from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
where cnt=FIELD_NUMBER start with FIELD_NUMBER=1 
connect by prior FILE_ID=FILE_ID and prior FIELD_NUMBER=FIELD_NUMBER-1

这会生成一个 SQL 控制 - 但是我希望执行此 SQL。

这是这个问题的延伸。

我尝试使用立即执行、光标dbms_sql但它不产生输出。用在蟾蜍身上。它所说的只是"PL/SQL程序成功完成"

使用以下方法

Declare 
  sql_stmt  VARCHAR2(3000);
  l_cursor  SYS_REFCURSOR;
  TYPE RefCurTyp    IS REF CURSOR;
  v_cursor          RefCurTyp;
  CURSOR c1 is
    select 'select '||FILE_ID||' FILE_ID,'||
    ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
    ' from RESPONSE_DETAILS where FILE_ID=' ||FILE_ID||';'
    from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
    where cnt=FIELD_NUMBER start with FIELD_NUMBER=1 
    connect by prior FILE_ID=FILE_ID and prior FIELD_NUMBER=FIELD_NUMBER-1;
BEGIN
  open c1;
  FETCH C1 into sql_stmt ;
  dbms_output.put_line(sql_stmt);
  close c1; 
  EXECUTE IMMEDIATE sql_stmt;
  open v_cursor for sql_stmt;
  return l_cursor;
  close l_cursor ;
END;

Answer 1

匿名 PL/SQL 块不能向调用方返回任何数据。 如果要将SYS_REFCURSOR返回到调用应用程序，则需要创建一个函数（或过程）。 例如

CREATE OR REPLACE FUNCTION get_results
  RETURN sys_refcursor
IS
  l_sql_stmt  VARCHAR2(3000);
  l_cursor    SYS_REFCURSOR;
BEGIN
  select 'select '||FILE_ID||' FILE_ID,'||
          ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
         ' from RESPONSE_DETAILS where FILE_ID = ' ||FILE_ID||';'
    into l_sql_stmt
    from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
   where cnt=FIELD_NUMBER 
   start with FIELD_NUMBER=1 
 connect by prior FILE_ID=FILE_ID 
        and prior FIELD_NUMBER=FIELD_NUMBER-1;
  dbms_output.put_line(l_sql_stmt);
  open l_cursor for sql_stmt;
  return l_cursor;
END;

我从您的代码中假设您希望您的 SELECT 语句返回单个 SQL 语句——您的代码仅从可能返回多个 SQL 语句的查询中获取一行。 我假设您只获取一个，因为您只希望 SELECT 语句返回一行。 否则，由于您的查询缺少ORDER BY，因此您将执行代码生成的 N 个 SQL 语句中的任意一个。

如果您经常调用此方法，则几乎肯定希望在动态 SQL 语句中使用绑定变量来file_id，而不是生成不可共享的 SQL 语句。 我在这里没有进行这种更改。

在

调用从 SSRS 返回sys_refcursor的存储函数时，还有另一个 StackOverflow 线程。