Easy Stored Procedure Output

Share Button

I answered a question on a DBA Forum today and I thought it was a common enough question to warrant a blog posting.

Question: I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data.

On a more generic note, can anyone explain to me why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:

CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO

and viola, a nice result set spits out in Query Analyzer (or a .net application).

Answer:

Before I go on, let me say I agree that PL/SQL is more powerful. That being said, here are your options.

1. Test it with REFCURSOR using a FUNCTION and selecting from dual:

SQL> create or replace function testfunc return sys_refcursor
  2  as
  3    c_test sys_refcursor;
  4  begin
  5    open c_test for select first_name, last_name, email from employees where rownum < 10;
  6    return c_test;
  7  end;
  8  /

Function created.

SQL> select testfunc() from dual;

TESTFUNC()
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

2. Use the same function and return it into a variable:

SQL> variable rc refcursor
SQL> exec :rc := testfunc()

PL/SQL procedure successfully completed.

SQL> print rc

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

3. Use your procedure with a variable:

SQL> create or replace procedure testproc(c_test out sys_refcursor) is
  2  begin
  3    open c_test for select first_name, last_name, email from employees where rownum < 10;
  4  end;
  5  /

Procedure created.

SQL> variable rc2 refcursor
SQL> exec testproc(:rc2);

PL/SQL procedure successfully completed.

SQL> print rc2

FIRST_NAME           LAST_NAME                 EMAIL
-------------------- ------------------------- -------------------------
Steven               King                      SKING
Neena                Kochhar                   NKOCHHAR
Lex                  De Haan                   LDEHAAN
Alexander            Hunold                    AHUNOLD
Bruce                Ernst                     BERNST
David                Austin                    DAUSTIN
Valli                Pataballa                 VPATABAL
Diana                Lorentz                   DLORENTZ
Nancy                Greenberg                 NGREENBE

9 rows selected.

#3 is more in-line with your original needs. Personally I’m a fan of #1 and #2 because of the capabilities of returning a refcursor as a function, like passing it into DBMS_XMLGEN.GETXML.

Share Button

11 comments

  1. Hey steve, thanks for the link you provided in DBA forums to this place. looks interesting! well i want to run a query several times with different inputs and store all outputs into a cursor/ record set and return to front end.

    I don’t clearly know how to implement this from the front end, planning to test with a sample today .
    Please can you comment if you have any ideas in this ?

  2. Hello Steve,
    if I need just a select statement, do you think I still should prefer using out sys_refcursor in a Stored Porcedure for performance, or Should I prefer a View instead of a Procedure.
    which one could be faster.
    My Select statement does not have a variable parameter, I mean if I write a procedure there will be no input parameters. There are just some constants for some criteria.

  3. Hi Steven,

    the above workaround given by you is really help full.

    I want to do some error handling like say NO-DATA-FOUND in the procedure.
    Could you please advise how should I proceed for?

  4. Hi ,

    Can anybody help me

    why I am getting the bellow error while I am trying to use Bulk Collect functionality in my code

    1 11 3 Execution ORA-06550: line 11, column 3:
    PL/SQL: ORA-00947: not enough values
    ORA-06550: line 7, column 3:
    PL/SQL: SQL Statement ignored

    my code

    Create or replace TYPE TESTTYPE AS OBJECT (
    POLID varchar2(20),
    INCEPTDATE Date,
    SUBID number(10));
    —————————————————
    create or replace type emptype is table of TESTTYPE
    ————————————————–
    Declare
    vout emptype;
    Begin
    select policyno,
    policy_inception_date,
    submission_id
    bulk collect into vout
    from mi_policy,mi_submission
    where policyno = policy_no
    and rownum<1000;
    for i in vout.first..vout.last
    loop
    dbms_output.put_line(vout(i).polid);
    end loop;
    END ;

  5. Hi i have question. How do i copy a table from one table to another created table of the same type using stored procedure without using cursor?

  6. Hi Steve, I read the orginal thread before finding your blog post and while you give a great answer to the problem, you don’t really answer the question as it relates to Oracle SQL developer. Yes, it’s straight-forward in SQL*Plus and even more simple in Toad but comparatively speaking Oracle SQL developer forces you through hoops…. well, that’s my opinion right now because I am new to SQL Developer and getting used to it. My searches online seem to suggest that the answer to “How do I test a stored procedure that returns a ref cursor using Oracle SQL Developer?” is – Don’t. I would be very happy to be proved wrong.

  7. CREATE OR REPLACE PROCEDURE SCOTT.insert_test(i_acctperiod IN VARCHAR2 := NULL,
    o_cursor out sys_refcursor )
    IS
    BEGIN

    DECLARE

    v_shorttermcutoffdatestr VARCHAR(20);
    l_query varchar2(1000);

    BEGIN
    delete from test;
    commit;

    SELECT TO_CHAR(add_months(E.HIREDATE,12),’MM/DD/YYYY’) into v_shorttermcutoffdatestr FROM EMP1 E WHERE
    TO_CHAR(E.HIREDATE,’Mon YYYY’) = i_acctperiod;

    l_query :=’ INSERT INTO TEST
    select ename,empno from emp1
    where hiredate variable rc2 refcursor
    SQL> exec insert_test(‘Dec 1980’,:rc2);

  8. The function compiles but I get this error when i try to execute it:
    identifier ‘testfunc’ must be declared.

    This code is so far useless.

  9. Susan, did you create it as a function (like #1 and #2) or a procedure (like #3)? Can you post your creation and execution along with the error? Perhaps if I can see what you did it won’t be so useless.

  10. Same issue here. I am using #3 however as I will eventually have multiple recordsets returned from a single stored procedure. I am also calling from Java like below:

    String sql = “{ call schema.procedure(?, ?) }”;

    stmt = connPrepareCall(sql);

    stmt.setInt(1, sectionId);
    stmt.registerOutParameter(2, OracleTypes.CURSOR);

    stmt.execute();

    rs = (ResultSet) stmt.getObject(2);

    … etc …

    Any help would appreciated

  11. Looks like this post is dead but I will ask anyway:
    I have a proc which takes 2 parameters and returns ref_cursor, I need to create a function which will call that proc (function will take the same 2 parameters as the SP and return a data set. How do I go about doing something like that?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.