How to get result set from Oracle Stored Procedure?

Question

This sounds like a simple, straight forward question, but I cannot figure it out. After hours of searching online I am more lost than I started.

I simply want to write some code to produce a result set from my procedure.

This is how the procedure starts:

CREATE OR REPLACE PROCEDURE TEST_PROC
(
    Cursor       OUT     SYS_REFCURSOR,
    SDate        IN      DATE,
    EDate        IN      DATE,
    Department   IN      VARCHAR2,
    Users        IN      CLOB
)
IS
-- ... 

Then it goes on the select fields from two different tables, joins them together, applies some filtering, and that's it.

When you want to return a result set from a table you write something like this:

SELECT * FROM TABLE;

I'd like to do something basic and easy like this, but get the data from the stored procedure. I know that when I call the procedure I'd have to provide the parameters.

I am using SQL in TOAD. Can anyone please provide help?

Answer 1

Sample procedure

CREATE OR REPLACE PROCEDURE TEST_PROC (P1 OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN P1 FOR SELECT * FROM user_tables;
END;

In Toad when you execute tell it to output the value of your OUT param in the Set Parameters window as seen in the screenshot. This is taken from Toad 12.7 so your dialog may look different, but the option has been there for years to you should have it somewhere in there.

Answer 2

CREATE OR REPLACE FUNCTION rs_func RETURN SYS_REFCURSOR IS
  l_result SYS_REFCURSOR;
BEGIN
  OPEN l_result FOR SELECT DUMMY FROM DUAL;
  RETURN l_result;
END;
/
SELECT rs_func FROM DUAL;

You can also apply arguments to the function and select from any table or combination of tables joined. Using the above template will execute the query as the definer of the function. If you want the query executed as the invoker of the function then specify AUTHID CURRENT_USER in the function specification. Stored procedures work in a similar manner but there are some usage complexities depending on the target environment you are using the procedure from.

Your questions requests the ability to select * from table but utilize a stored procedure. You cannot execute a stored procedure from a SQL query only functions. There is an ability to execute: select * from table (stored_func (...)); This utilizes a "Pipelined" function and requires the creation of object types to define the return structure of the function not a result set.