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;