Equivelant of %Rowtype if the row structure does not exist in a table?

From: <skull_leader7_at_yahoo.com>
Date: 25 Feb 2005 11:40:53 -0800
Message-ID: <1109360452.991285.10220@l41g2000cwc.googlegroups.com>

Hi all...

>From some examples on the web, I managed to create a pipelined function
that takes a cursor as input and returns all of the rows. My code is shown below at the bottom of the message.

What I really want to do, however, is slightly more complex. Whereas the cursor I pass right now to the function is generated from the relatively simple "select * from case_rec" statement (see last line of my code), I really want to do something like "select case_rec.con_type, case_extra.note from case_rec, caes_extra where case_rec.case_id = case_extra.case_id" -where I am selecting data from multiple tables.

What I need help on is in declaring the cursor type. Whereas for my existing function I declare the cursor type as:

TYPE refcur_t IS REF CURSOR RETURN case_rec%rowtype;

This is easy because I am selecting all the rows from case_rec, so I can just use "case_rec%rowtype".

How do I create an equivelant declaration for a row structure that does not really exist in any table, but only in my second select statement from above?

The only thing I can think of that might work would be to create a dummy table that mirrors the row structure I want, then use its %rowtype in my declaration, but it seems that there ought to be a more direct approach?

Sorry if this is a simple question - this stuff is pretty much far beyond anything I've done with Oracle before.

Thanks in advance for any help.

My existing code

---

create or replace type case_rec_type as object ( case_id number, audit_id number, audit_date date, flow number, con_type char(10), con_method number, remedy varchar2(20), form_type number, sub_type number, callback number );
/

CREATE OR REPLACE TYPE case_rec_type_set AS TABLE OF case_rec_type; /

CREATE OR REPLACE PACKAGE refcur_pkg IS

TYPE refcur_t IS REF CURSOR RETURN case_rec%rowtype;

END refcur_pkg;
/

CREATE OR REPLACE FUNCTION getDataFromCursor( p refcur_pkg.refcur_t)
RETURN case_rec_type_set PIPELINED IS

out_rec case_rec_type := case_rec_type(NULL,NULL,NULL,null, null, null, null, null, null, null);
in_rec p%ROWTYPE;

BEGIN
   LOOP

      FETCH p INTO in_rec;
      EXIT WHEN p%NOTFOUND;

      out_rec.case_id := in_rec.case_id;
      out_rec.audit_id := in_rec.audit_id;
      out_rec.audit_date := in_rec.audit_date;
      out_rec.flow := in_rec.flow;
      out_rec.con_type := in_rec.con_type;
      out_rec.con_method := in_rec.con_method;
      out_rec.remedy := in_rec.remedy;
      out_rec.form_type := in_rec.form_type;
      out_rec.sub_type := in_rec.sub_type;
      out_rec.callback := in_rec.callback;
      PIPE ROW(out_rec);

   END LOOP;
   CLOSE p;
   RETURN;
END getDataFromCursor;
/

select * from table(getDataFromCursor(cursor(select * from case_rec))); Received on Fri Feb 25 2005 - 13:40:53 CST