How do I get the rows (and columns) from a stored procedure to display normally?

ToolTimeTabor

I am not finding any examples on how to get a traditional grid (rows and columns) returned from a store procedure.

My source is an Oracle Stored Procedure and I need to transfer the output of that procedure into a table.

Suppose I have a procedure:

CREATE OR REPLACE PROCEDURE spAddress(addresses IN OUT SYS_REFCURSOR) 
AS
BEGIN
  OPEN addresses FOR SELECT ID, SALUTATION, ZIP FROM ADDRESS;
END;

In MS SQL server, I would simply EXECUTE it, as in the following:

EXECUTE spAddress();

and I would get a data grid displayed with the list of addresses where there were rows and columns displayed the same as if I had run

SELECT ID, SALUTATION, ZIP FROM ADDRESS;

The result would look something like this in the my developer:

Every example that I have seen uses DBMS_OUTPUT to create a concatenated line of text that includes ID, SALUTATION and ZIP, but its one single column of strings. What I want is the rows and distinct columns with column data types, as if I had run the SELECT directly.

I know they are there, because I can see the results from running it...

The output variables clearly show the rows and columns, as they appear in the query without needing to use DBMS_OUTPUT and concatenations.

How do I get a proper data grid from executing a procedure? Something that looks like this:

My ultimate objective is to use the output of a procedure as the input to an INSERT command. For example, let's say I am trying to populate a table called ADDRESS_LOCATIONS from my example. What I want to be able to do is along the lines of:

INSERT INTO ADDRESS_LOCATIONS (ID, SALUTATION, ZIP)
SELECT ID, SALUTATION, ZIP FROM spAddress;

Note: the actual source procedure is more complicated, so I cannot use a simple SELECT against the table directly, as shown here:

INSERT INTO ADDRESS_LOCATIONS (ID, SALUTATION, ZIP)
SELECT ID, SALUTATION, ZIP FROM ADDRESS;

Solomon Yakobson

Oracle != SQL Server. We don't create SP if all is needed is select from a table.

SY.

ToolTimeTabor

Really, Oracle != SQL Server.

Of course it is not. I am providing context so that people can understand what I need.

In Oracle, what is the equivalent?

thatJeffSmith-Oracle

write a select statement that calls a function that returns a refcursor

or execute a refcursor in the procedure editor and view the output panel

or like others have suggested, if you want sql behavior, just run the sql w/o the plsql wrapper

Solomon Yakobson

In Oracle, what is the equivalent?

Plain select. All you do is simply issue

SELECT ID, SALUTATION, ZIP FROM ADDRESS;

SY.

User_H3J7U

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16827588#Comment_16827588

Now the execution of the pl/sql outputs all dbms_sql.return_result to the script window. It would be convenient if the Run Statement output all dbms_sql.return_result to several grids, the Run Script to the Script Window.

thatJeffSmith-Oracle

you can even run the sql directly in the procedure editor...w/o executing the procedure