PL/SQL ref cursors / multiple tables

[spperl]

Hi,

Thanks for your previous comment regarding ref cursors yesterday... I've had a look and now think I'm a bit closer to being able to retrieve a recordset from multiple tables however still having difficulty.

Here is the code written so far:

Package definition to hold the ref cursor:

create or replace
PACKAGE Types AS 
  TYPE cursor_type IS REF CURSOR;
END Types;

My procedure to retrieve results from multiple tables (the tables are made up for this example and assume all joins are correct):

create or replace
PROCEDURE myRS (p_in        IN  NUMBER,
                p_recordset OUT Types.cursor_type) AS 
BEGIN 
  OPEN p_recordset FOR
    select t1.name, t2.address, t3.delivery_address
    from table1 t1, table2 t2, table3 t3
    where t1.id = t2.id
    and t2.id = t3.id
    and t1.id = p_in;
END myRS;

To test the above I run the following:

DECLARE
  v_cursor  Types.cursor_type;
  v_name              t1..ename%TYPE;
  v_address           t2.address%TYPE;
  v_delivery_address  t3.delivery_address%TYPE;
BEGIN
  myRS (p_in        => 1,
            p_recordset => v_cursor);
            
  LOOP 
    FETCH v_cursor
    INTO  v_name, v_address, v_delivery_address;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ' | ' || v_address || ' | ' || v_delivery_address);
  END LOOP;
  CLOSE v_cursor;
END;

however when I run the above test I simply get 'anonymous block completed' ... changing the procedure and test to get data from one table only works ok.

Can anyone provide any hints, tips as to where I'm going wrong. I would really appreciate this.

Many thanks.
spperl

 

[spperl]

Hi,

Please ignore, all was ok with the above code... the id I passed into the procedure for test purposes was wrong.

Doh!

Thanks again to carp yesterday for pointing me in the right direction.

Cheers,
spperl

 

[carp]

You're very welcome. One other suggestion would be to place the call to dbms_output before your exit rather than after. This way, you'll be sure to get all of your messages.