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;

Possible duplicate of [Create an Oracle function that returns a table](http://stackoverflow.com/questions/2829880/create-an-oracle-function-that-returns-a-table) — Muhammad Muazzam, Mar 26 '17 at 06:40

score 3 · Accepted Answer · answered Mar 26 '17 at 14:01

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;

Thanks I tried this approach and it seems to have done the trick, sorry for the late reply it took a while for me to work out why it works so i know the problem in the future. — Delta1x, Mar 26 '17 at 19:51

Albert Godfrind · Answer 2 · 2017-03-27T15:14:05.033

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.

Thanks for the reply, I ended up using a combination of this answer and the one above. For some reason and I think its my dataset,the loop option kept throwing 'no data found' for some rows when i tried to process further (i'm using a function to calculate size and displaying the results). But this solution would have been the better one without some of the problem rows i have and the database is far too big to find them all. On the plus side i've learnt how to loop without cursors so thank you. — Delta1x, Mar 28 '17 at 01:32

score 0 · Answer 3 · answered Mar 26 '17 at 03:02

0

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;

answered Mar 26 '17 at 03:02

Avrajit Roy

3,233
1
13
25

`TOO Many Rows Error` error comes when you try to put multiple rows in a single dimension declared variable of a data type. It doesnot come when you try to return as `OUT` it via a procedure / function. Also OP intention is not to return resultset as `OUT` parameter. – XING Mar 26 '17 at 14:09
My point was to say that either use cursor or collection to handle this kind of situation. – Avrajit Roy Mar 28 '17 at 12:44
And in my answer OUT means : putting multiple values in scalar variable only. Hope you understood. – Avrajit Roy Mar 28 '17 at 12:45

Return multiple rows from procedure , select statement PL/SQL

3 Answers3