Help with procedure to return values

[campbere]

Hello,

I need some help returning values from a procedure. All I want is to return results from a select statement. Many rows would be returned. I had read that bulk collect into was a good way to do this but I am not sure how to return the values.

Can someone help?

CREATE OR REPLACE FUNCTION SITE_INFO AS
BEGIN
DECLARE

TYPE SiteId_Tab is TABLE OF LINK.SiteId%TYPE;
TYPE ProviderId_Tab is TABLE OF LINK.ProviderId%TYPE;
TYPE SiteUrl_Tab is TABLE OF LINK.SiteUrl%TYPE;

SiteIds SiteId_Tab;
ProviderIds providerId_Tab;
SiteUrls SiteUrl_Tab;

BEGIN
SELECT SiteId,
ProviderId,
SiteUrl
BULK COLLECT INTO SiteIds,
ProviderIds,
SiteUrls,
FROM LINK
WHERE Site_Flag = 1;
END;

END SITE_INFO;

 

[sem]

To expose your results to outer world you should make them clear. As long as your types are defined within your procedure, they are not visible to anybody else and can not be used outside. So you should either define them in some package specification or create database-wide types for them. You may also don't use intermediary collection, but return REF CURSOR instead.

Regards, Dima

 

[SantaMufasa]

Campbere,

At this point, it would be good to re-state your business need/purpose for your code. Just what do you wish to do with the results of your database access? Do you simply wish to see a list of Site IDs, Provider IDs, and SiteURLs? If so, a simple SELECT from SQL*Plus should do.

Please advise,

Dave

 

[campbere]

The point is to take this simply statement and return the results. Different applications use the data for different reasons. So I wanted to put the basic statement in a stored procedure for all of them to access.

For example one application takes the data and builds an xml file with the data.