0

I am new to Oracle and sql navigator. I can't seem to find a way to execute a stored proc and see its results. The proc is created as follows. How do I execute this proc in sql navigator. I am using sql navigator 6.7. Also, please be advised that the proc is using out ref-cursor.

PROCEDURE getdata
( p_id            IN VARCHAR2,
 p_date            IN DATE,
 p_out   OUT ref_cursor)
IS

BEGIN
 OPEN p_out FOR
    SELECT *
    FROM   OPS_ARW.logs
    WHERE   member_id = p_id 
    AND     date>p_date;

END;

PS: I am pretty sure this is a duplicate. Would appreciate if you can only redirect me to the correct place as I can't find it.

user1
  • 1,063
  • 1
  • 8
  • 28
  • Should your out parameter be declared as a SYS_REFCURSOR? – abhi Apr 02 '14 at 16:28
  • I have no idea. I am extremely new to oracle. I just want to run this proc and see the result. I can't make change to the proc though. – user1 Apr 02 '14 at 21:03
  • I am unable to do this with [sqlfiddle](http://www.sqlfiddle.com/) but I found another question related to your question on [SO](http://stackoverflow.com/questions/19110842/how-to-print-sys-refcursor-with-plsqldeveloper-window). – abhi Apr 02 '14 at 21:22

1 Answers1

0

It is a long time after the question, but as I spend a lot of time to build this code, here my running solution for a similar problem. Note: my procedure returns the columns id, codboard, descrboard, and TABNUM. I am using Sql Navigator:

declare
l_list_boards sys_refcursor;
l_id          number;
l_codboard    VARCHAR2(50 BYTE);
l_descrboard  VARCHAR2(250 BYTE);
l_TABNUM      NUMBER;
begin

    PACK_NAME.GetBoardsList(list_boards=> l_list_boards);

    DBMS_OUTPUT.put_line ('Results: ');  

    loop
        fetch l_list_boards into l_id, l_codboard, l_descrboard, l_TABNUM;
        DBMS_OUTPUT.put_line(l_descrboard);
        EXIT WHEN l_list_boards%NOTFOUND;
    end loop;

    close l_list_boards;
end;
Rogério Silva
  • 121
  • 1
  • 11