Search code examples
oracle-databaseoracle11gtoadtoad-data-point

View results of pl/sql stored procedure in Toad?

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?

Solution

  • 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.

    enter image description here

    Here's the result:

    enter image description here

    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.