How do I return a set of results from a Query Window in Oracle SQL Developer?

Question

For this code:

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;

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;


END;

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:

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:

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

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?

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

Now I get this error:

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:

Gary Myers · Accepted Answer · 2015-12-30 04:09:13Z

2

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

answered Dec 30, 2015 at 4:09

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam · Accepted Answer · 2015-12-30 15:41:05Z

I finally got this going. There is no way I guess to just return the results and watch them fill up a grid nice and pretty like in SQL Server. You always have to loop through a cursor to display results.

So basically here I call Package Proc to populate my first cursor and populate a table TYPE based on a RECORD TYPE from the first cursor.

Now I define a second cursor, and use Common Table Expressions to join to another table into the second cursor.

Now I have to loop through the second cursor and dbms_output.put_line the results.

What I was really trying to do here was take Appls_CTE coming back from GetPortfolioApplsAndProjects and join that to another table in our db to debug some of our data.

Now I think I have it figured out where I can freely join Appls_CTE to anything and start debugging this way like looking for nulls in our data.

Here is the final version:

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;
fbp_appl_id             NUMBER(10,0);
fbp_council             VARCHAR2(6 BYTE);

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 || ' - ' || v_ADMIN_PHS_ORG_CODE || ' - ' ||  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;
    --DBMS_SQL.RETURN_RESULT(GrantApplications_CUR);

    LOOP
        FETCH GrantApplications_CUR
        INTO fbp_appl_id, fbp_council;
        EXIT WHEN GrantApplications_CUR%NOTFOUND;
        dbms_output.put_line(fbp_appl_id || ' - ' || fbp_council);
    END LOOP;
    CLOSE GrantApplications_CUR;
END;

Collectives™ on Stack Overflow

How do I return a set of results from a Query Window in Oracle SQL Developer?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related