proc return a union

[crystalhelp]

I created a stored procedure that looks like this:

CREATE OR REPLACE PROCEDURE procname
( --declare parameters) AS

--declare variables

begin

--assign value to variables

SELECT
FROM
WHERE

UNION

SELECT
FROM
WHERE

UNION

SELECT
FROM
WHERE;

--I don't need an order by on the union

END procname;

--

That's it. It's pretty simple. But I get the message 'PLS-00428: an INTO clause is expected in this SELECT statement'. Is that true? Obviously it is, but I don't see why. Is there some other way I can do this?

 

[carp]

Yes, it's necessary. PL/SQL needs some place to hold the results of your query - that's why you SELECT...INTO. It's also how you populate whatever it is you have in your
--assign value to variables
section.

 

[crystalhelp]

I am use to working with Sybase. And in Sybase, the final select (whether it is a union or not - I think) is what is returned to what called the stored proc. If I select into a temp table, I am going to have to select from that temp table to return my results. Doesn't that seem like placing everything in a temp table just so you can select from it again an unnecessary step? Or am I missing something? Do you return result sets differently in Oracle?

 

[sem]

In oracle the situation is a bit different. You may return REF CURSOR, that is a kind of pointer to the resut set, or a "temporary table itself" as a variable of some collection type. In the first case you should declare a type of this ref cursor in some package specification to make it "viewable" by calling program also. In the second case you should also declare "external" variable to hold the results, but you should also populate it by a set of SELECT INTO calls. Regards, Dima