Return multiple rows from procedure , select statement PL/SQL

Question

This is the procedure section of an anonymous block from which it takes the parameter pID, parcel_id. My problem is the select statement is meant to find and display all parcels that touch the query parcel and it works perfectly fine in a normal SQL query when I put in,

select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
    from Parking target, Parking query
    where query.parcel_id = 68
    and  target.district_id = 1
    and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

That example returns five rows with 5 different parcels that touch the parcel 68. However, when I do it in the procedure I either get the 'TOO Many Rows Error' or if I try to add 'And Rownum <2' it works okay but doesn't show all the relationships, i.e. it runs through and displays just one relationship for each parcel. Is there anything I could do to improve this? I've been reading other posts on the site and there are references to reference cursors but I don't know how these apply here. Many thanks.

procedure Payx (pID number )is 

  varDistrict Parking.District_id%type;
  vID Parking.parcel_id%type;
  vQED Parking.parcel_id%type;
  varRel varchar2(20);
  begin

select target.district_id, target.parcel_id,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
      into varDistrict
           vID,
           vQED,
           varRel
      from Parking target, Parking query
      where query.parcel_id = pID
      and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE'
      and rownum <2;
    ---  dbms_output.put_line('')

  end Payx;

Answer 1

The question is really: what do you want to do with the results ?.

Pretty much all typical SELECT statements used in applications return multiple results - possibly a lot of them. Applications then need to be ready to handle those results one after the other. The way this is done depends on the language your application is written in. For Java for instance the query returns a ResultSet object over which you iterate using its next() method. Python uses similar techniques.

In PL/SQL, just use a for loop. No need to use explicit cursors:

for t in (
  select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
  from Parking target, Parking query
  where query.parcel_id = 68
  and  target.district_id = 1
  and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE'
)
loop
   -- process the results here
end loop;

Inside the loop, refer to the columns returned in each result by prefixing them with the loop variable (here t). For instance:

  dbms_output.put_line ('district_id='||t.district_id);

Obviously I imagine that the purpose of your application is not to print out the results in sqlplus. You can do computation on the results, write them out to another table ...

Now if your purpose is to save the result in some table for later processing, then just to this:

create table query_results as 
select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
from Parking target, Parking query
where query.parcel_id = 68
and  target.district_id = 1
and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

or

insert into query results
select target.district_id, target.parcel_id ,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
from Parking target, Parking query
where query.parcel_id = 68
and  target.district_id = 1
and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';

So what application language do you use ? And what do you do with the results of your query ?

EDIT:

You can simplify your syntax a little by writing:

and  sdo_touch(target.geom, query.geom) = 'TRUE'

And returning sdo_geom.relate(target.geom, 'determine', query.geom, 0.05) is pointless since you check for the TOUCH relationship: the result will always be TOUCH. It just adds to the CPU cost of running the query.

Answer 2

As mentioned by @Muhammad Muazzam, since the query is returning multiple rows you need either a collection to hold the records at a single go or you can loop through the select and hold the records in the variables you declared. I show you how you can do it using a RECORD.

create or replace procedure payx (pid   number) 
is
   --Created a record by bundling all your single defined variables  
     type xx is RECORD
     (
         vardistrict                   parking.district_id%type,
         vid                           parking.parcel_id%type,
         vqed                          parking.parcel_id%type,
         varrel                        varchar2 (20);

     );

     type var is table of xx index by pls_integer;

     var1 var;       

begin
     select target.district_id,
            target.parcel_id,
            query.parcel_id,
            sdo_geom.relate (target.geom,
                             'determine',
                             query.geom,
                             0.05
                            ) relationship
       bulk collect into var1
       from parking target,
            parking query
      where query.parcel_id = pid
        and sdo_relate (target.geom,
                        query.geom,
                        'mask=TOUCH'
                       ) = 'TRUE' ;

      for i in 1..var1.count
      loop                       
       dbms_output.put_line (   var1 (i).vardistrict
                            || var1 (i).vid
                            || var1 (i).vqed
                            || var1 (i).varrel);

      end loop;
end payx;

Answer 3

Since are trying to OUT multiple values, you need to use ether Cursor or with the hlp of collections. Hope this helps.

procedure Payx (pID number,
                p_ref_out OUT sys_refcursor )is 

  varDistrict Parking.District_id%type;
  vID Parking.parcel_id%type;
  vQED Parking.parcel_id%type;
  varRel varchar2(20);
  begin

OPEN p_ref_out FOR
select target.district_id, target.parcel_id,query.parcel_id, sdo_geom.relate(target.geom, 'determine', query.geom, 0.05)Relationship
      into varDistrict
           vID,
           vQED,
           varRel
      from Parking target, Parking query
      where query.parcel_id = pID
      and  sdo_relate(target.geom, query.geom, 'mask=TOUCH') = 'TRUE';
    ---  and rownum <2;
    ---  dbms_output.put_line('')

  end Payx;