Oracle function to return a table included in a where clause

Question

I would like to create a function which returns a table of results. Something like

select * from address where zipcode in (f_zips_in_radius(45.123,-93.123,50));

I have been working on that function but don't have anything working so will exclude my attempts so as not to muddle the question.

Assuming that it is possible, How would I implement it?

Possible help here: http://geertverhoeven.blogspot.com/2007/07/create-function-that-returns-table.html — Ray Toal, Dec 06 '11 at 14:47
Check out the manual: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/tuning.htm#i53109 — , Dec 06 '11 at 14:57
See a SO question [Database: Pipelined Functions](http://stackoverflow.com/q/2889057/) — user272735, Dec 06 '11 at 15:27

score 2 · Accepted Answer · answered Dec 07 '11 at 21:55

A combination of comments lead me to that answer. Thanks @a_horse_with_no_name and @Ray Toal

Here was my final solution

CREATE OR REPLACE PACKAGE pkg_distance AS
  TYPE vcharset_t IS TABLE OF VARCHAR2(20);
  FUNCTION zips_in_radius(i_lat number, i_lon number, i_radius NUMBER) RETURN vcharset_t PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_distance AS
FUNCTION zips_in_radius(i_lat number, i_lon number, i_radius NUMBER) RETURN vcharset_t PIPELINED IS
  BEGIN
      for r in (
        select zipcode from zipdata z where f_distance(i_lat, i_lon, z.lat, z.lon) <= i_radius
      )
      loop
        pipe row ( r.zipcode);
      end loop;
      return;
  END;
END;

Then to use it I am using a query like:

--Boston City Centered on 42.360637,-71.0587120
select * from address a
 where substr(a.zipcode,1,5) in 
   (select * from TABLE(pkg_distance.zips_in_radius(42.360637,-71.0587120,60)))

Which in my opinion still has an extra "select * from TABLE(" for my comfort, but still managable.

score 0 · Answer 2 · answered Dec 06 '11 at 19:48

0

The problem is zipcode can be anything so if u r treating like a number then it becomes easier. You have to compute every possible permutation between x and y and then pass it back.

Since I donot know your app and your case, how many results can you expect ? Because that in clause can be very costly and you might have to do materialize the result set.. Or do some clever tricks....

Also I am guessing you have to cast this into a table...

answered Dec 06 '11 at 19:48

Ender Wiggin

414
1
6
16

The example above is not specific to the issue, I'll take care of normalizing zipcodes in the actual implementation. – Lloyd Dec 06 '11 at 21:52

Oracle function to return a table included in a where clause

2 Answers2