niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
Equivelant of %Rowtype if the row structure does not exist in a table?
Post new topic   Reply to topic Page 1 of 1 [2 Posts] View previous topic :: View next topic
Author Message
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Fri Feb 25, 2005 9:09 pm    Post subject: Re: Equivelant of %Rowtype if the row structure does not exist in a table? Reply with quote
skull_leader7@yahoo.com wrote:

Quote:
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;
Cursor(cursor(select * from case_rec)));


Defined your ref cursor as based on a cursor%ROWTYPE;

CURSOR mycur IS
SELECT ...
FROM ... a, ... b, ... c
WHERE a.id = b.id
AND a.id = c.id
AND ...

TYPE refcur_t IS REF CURSOR RETURN mycur%ROWTYPE;

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
skull_leader7@yahoo.com
*nix forums beginner


Joined: 25 Feb 2005
Posts: 1

PostPosted: Fri Feb 25, 2005 6:40 pm    Post subject: Equivelant of %Rowtype if the row structure does not exist in a table? Reply with quote
Hi all...

Quote:
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)));
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [2 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 5:21 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts How to ALTER a table using the ALTOBJ procedure Serge Rielau IBM DB2 1 Fri Jul 21, 2006 1:06 pm
No new posts Can't Select External Table from CSV File Resant Server 1 Fri Jul 21, 2006 2:45 am
No new posts access method "gin" does not exist Kevin Murphy PostgreSQL 0 Thu Jul 20, 2006 4:25 pm
No new posts Images Table Nicholas Vettese MySQL 1 Thu Jul 20, 2006 2:19 pm
No new posts Datablock structure: changes from a version to another ? Spendius Server 2 Thu Jul 20, 2006 10:26 am

Mobile Phones | Loans | Free Games | Sprint Ringtones | Credit Card Offers
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1907s ][ Queries: 20 (0.0634s) ][ GZIP on - Debug on ]