Forum Stats

  • 3,838,711 Users
  • 2,262,394 Discussions
  • 7,900,739 Comments

Discussions

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

ToolTimeTabor
ToolTimeTabor Member Posts: 279 Blue Ribbon
edited Mar 2, 2022 6:15PM in SQL Developer


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;


Answers