How to return dynamic cursor from oracle stored procedure

Question

I have 2 tables in which ID field is common.

I am fetching all records of first table in a cursor. Then I want to do is that on the basis of each ID from cursor, I want to get the values from second table and then return that.

How can I do that... Please help !!!

Answer 1

homework?

this is basic SQL. generally you'd join the two tables.

begin
  for r_row in (select b.*
                  from tab1 a 
                       inner join tab2 b
                               on b.id = a.id)
  loop
    null; -- do whatever
  end loop;
end;
/

if you have an existing cursor and can't change it

eg where your_cursor is just returning an ID column.

begin
  open your_cursor;
  loop
    fetch your_cursor into v_id;
    exit when your_cursor%notfound;
    for r_row in (select * from tab2 b where b.id = v_id)
    loop
      null; -- do whatever here.
    end loop;
  end loop;
end;
/

edit: as per comments:

some sample data:

SQL> create table table1 (id number primary key, name varchar2(20));

Table created.

SQL> create table table2 (id number, col1 varchar2(20), col2 varchar2(20));

Table created.

SQL> insert into table1 values (1, 'test');

1 row created.

SQL> insert into table1 values (2, 'foo');

1 row created.

SQL>
SQL> insert into table2 values (1, 'John', 'Smith');

1 row created.

SQL> insert into table2 values (1, 'Peter', 'Jones');

1 row created.

SQL> insert into table2 values (1, 'Jane', 'Doe');

1 row created.

SQL> insert into table2 values (2, 'Nina', 'Austin');

1 row created.

SQL> insert into table2 values (2, 'Naman', 'Goyal');

1 row created.

SQL> commit;

Commit complete.

create a type to hold the return structure. note the datatypes NEED to match the datatypes of the tables table1 and table2 (%type won't work, so make sure they match)

SQL> create type my_obj as object (
  2    id number,
  3    name varchar2(20),
  4    col1 varchar2(20),
  5    col2 varchar2(20)
  6  );
  7  /

Type created.

SQL> create type my_tab as table of my_obj;
  2  /

Type created.

now create your function (you can put this in a package if, in your real code, you have it that way).

SQL> create function function1
  2    return my_tab pipelined
  3  is
  4  begin
  5    for r_row in (select t1.id, t1.name, t2.col1, t2.col2
  6                    from table1 t1
  7                         inner join table2 t2
  8                                 on t1.id = t2.id)
  9    loop
 10      pipe row(my_obj(r_row.id, r_row.name, r_row.col1, r_row.col2));
 11    end loop;
 12  end;
 13  /

Function created.

SQL>
SQL> select *
  2    from table(function1);

        ID NAME                 COL1                 COL2
---------- -------------------- -------------------- --------------------
         1 test                 John                 Smith
         1 test                 Peter                Jones
         1 test                 Jane                 Doe
         2 foo                  Nina                 Austin
         2 foo                  Naman                Goyal

you could pass inputs if required into that function eg table(function1('a', 'b')); etc..