View results of pl/sql stored procedure in Toad?

Question

I'm new to Oracle, and I use Toad Data Point to create and test stored procedures.

I created this simple stored procedure:

CREATE OR REPLACE PROCEDURE dummy_sp (
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
      select sysdate, user from dual;
END dummy_sp ;
/

I executed this, and the result from Toad is Executed Successfully.

Now, I would like to view the results of this stored procedure. In Toad Data Point I type the following:

variable mycursor refcursor;
call dummy_sp ( :mycursor );

I get a popup asking for a parameter. I click OK and I get the error ORA-00900: invalid SQL statement.

How can I see the result of SP dummy_sp in Toad Data Point?

In SQL Server I can run exec usp_sales and see the results of a select statement. There has to be something like that in Oracle and Toad, right?

Answer 1

Here you go, using Toad Data Point.

Execute the stored procedure with a bind variable in it, like :mycursor, and then make sure to configure the type as CURSOR and direction as OUT when Toad Data Point prompts you for the bind variable settings.

Here's the result:

Finally, if you wish to avoid the popup for bind variables, you can execute the procedure directly from the object explorer:

Right-click the procedure and choose Operations / Execute Procedure, and Toad will run it, without prompting for data type.

Answer 2

In case you need a workaround while you wait for help with your tool, the default, free IDE for Oracle Database makes this pretty easy.

If you execute the program using the code editor, it will automatically grab any outputs, whether those be OUT params or RETURNs from a function, including your refcursor

Or if your GUI has proper SQLPlus script execution support (SQL Developer does, not sure about your program):

var x refcursor
exec dummy_sp(:x);
print :x;

And the output:

PL/SQL procedure successfully completed.



SYSDATE             USER                                                                                                                            
------------------- --------------------------------------------------------------------------------------------------------------------------------
27-JUN-19  13.58.47 HR