如何从 Oracle SQL Developer 的查询窗口返回一组结果? [英] How do I return a set of results from a Query Window in Oracle SQL Developer?

问题描述

对于此代码:

DECLARE 
    v_APPL_ID               NUMBER(10,0) := 0;
    v_ADMIN_PHS_ORG_CODE    VARCHAR2(2 BYTE) := ' ';
    v_SERIAL_NUM            NUMBER(6,0) := 0;
    v_Proj_Appl_Rec         Proj_Appl_Rec;
    v_Proj_Appl_Tab         Proj_Appl_Tab := Proj_Appl_Tab();
    v_Proj_Appl_Cur         SYS_REFCURSOR;
    v_cnt                   NUMBER := 0;
    GrantApplications_CUR   SYS_REFCURSOR;

开始ireport_portfolios.GetPortfolioAppsAndProjects(null, 190, '动态', null, v_Proj_Appl_Cur);

BEGIN ireport_portfolios.GetPortfolioAppsAndProjects( null, 190, 'DYNAMIC', null, v_Proj_Appl_Cur);

--dbms_output.put_line('appl_id' || chr(9) || 'ic' || chr(9) || 'serial_num');   
LOOP
  FETCH v_Proj_Appl_Cur
  INTO v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM;
  EXIT WHEN v_Proj_Appl_Cur%NOTFOUND;
  v_Proj_Appl_Tab.extend;
  v_cnt := v_cnt + 1;
  v_Proj_Appl_Tab(v_cnt) := Proj_Appl_Rec(v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM);
  --dbms_output.put_line( v_APPL_ID || chr(9) || v_ADMIN_PHS_ORG_CODE || chr(9) ||  v_SERIAL_NUM);
END LOOP;
CLOSE v_Proj_Appl_Cur;

     OPEN GrantApplications_CUR FOR
      WITH Appls_CTE
      AS
      (
         SELECT DISTINCT
                pa.Appl_Id
           FROM TABLE(v_proj_appl_tab) pa
      )

      SELECT ga.appl_id, council_meeting_date
      FROM Appls_CTE ac
      JOIN grant_appls ga
      ON ac.appl_id = ga.appl_id;

    RETURN   GrantApplications_CUR;

结束；

如果我注释掉底部的 RETURN 语句，它运行良好.但我看到的是:匿名块完成.所以我添加了 Return 语句，现在我得到了:

it runs fine if I comment out the RETURN statement at the bottom. But all I see is: anonymous block completed. So I added the Return statement and now I get:

错误报告 -ORA-06550:第 41 行，第 9 列:PLS-00372:在过程中，RETURN 语句不能包含表达式ORA-06550:第 41 行，第 9 列:PL/SQL:忽略语句06550. 00000 - 第 %s 行，第 %s 列:\n%s"*原因:通常是 PL/SQL 编译错误.*动作:

Error report - ORA-06550: line 41, column 9: PLS-00372: In a procedure, RETURN statement cannot contain an expression ORA-06550: line 41, column 9: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

我尝试将 CTE 取出并加入TABLE(v_proj_appl_tab) pa".但我得到了同样的错误.

I tried taking the CTE out and just joining to "TABLE(v_proj_appl_tab) pa". But I get the same error.

最后一步是什么才能看到结果?如何从一个简单的查询窗口返回这样的结果而不必编写 Package PROC?

What is the last step here to be able to see the results? How do you return results like this from a simple query window without having to write a Package PROC?

我尝试从下面的提示运行它:DBMS_SQL.RETURN_RESULT(GrantApplications_CUR);

I tried running this from the tip below: DBMS_SQL.RETURN_RESULT(GrantApplications_CUR);

现在我收到此错误:

错误报告 -ORA-06502: PL/SQL: 数值或值错误ORA-06512:在LINK_OD_IREPORT.IREPORT_PORTFOLIOS"，第 217 行ORA-06512:在LINK_OD_IREPORT.IREPORT_PORTFOLIOS"，第 83 行ORA-06512:在第 12 行06502. 00000 - PL/SQL:数字或值错误 %s"*原因:算术、数字、字符串、转换或约束错误发生.例如，如果尝试执行此操作，则会发生此错误将值 NULL 分配给声明为 NOT NULL 的变量，或者如果尝试将大于 99 的整数分配给变量声明 NUMBER(2).*操作:更改数据、操作方式或声明方式值不违反约束.现在我收到此错误:

Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at "LINK_OD_IREPORT.IREPORT_PORTFOLIOS", line 217 ORA-06512: at "LINK_OD_IREPORT.IREPORT_PORTFOLIOS", line 83 ORA-06512: at line 12 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. Now I get this error:

推荐答案

在 12c 中，他们通过 DBMS_SQL.RETURN_RESULT 添加了对来自 PL/SQL 的隐式语句结果的支持.

In 12c they added support for Implicit Statement Results from PL/SQL via DBMS_SQL.RETURN_RESULT.

declare
  l_cursor_1 SYS_REFCURSOR;
BEGIN
  OPEN l_cursor_1 FOR
    SELECT table_name
    FROM   user_tables
    WHERE  rownum < 5;
  DBMS_SQL.RETURN_RESULT(l_cursor_1);
END;
/

这篇关于如何从 Oracle SQL Developer 的查询窗口返回一组结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！