473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

FETCH arbitrary columns from a ref_cursor

The problem is as follows:
I have a stored proc. that returns a types.ref_cursor (where
types.ref_cursor is defined as 'ref cursor'):
PROCEDURE P(c OUT TYPES.ref_cursor);
The returned cursor has a bunch of columns, but I only need a few of them.
Anyway, with FETCH INTO I have to provide variables for every column. So I
have to do this:
var1 Type1;
var2 Type2;
...
FETCH c INTO var1, var2, ..., var20, ... var_n;
Where Type1, etc. ... are as specified in the documentation of P. (Since I
couldn't find a way to determine the record type of an arbitrary
ref_cursor.)
Is there a way to make this type of situation easier?
Some solutions came into my mind, such as:

1. I could declare a variable with the type c%ROWTYPE or something.
2. I could FETCH only the columns that I need, e.g.
FETCH c(col1, col5, col7) INTO var1, var5, var7;
3. I could somehow find a way for the procedure to return something other
than a ref_cursor which seems a nightmare to work with.

Unfortunately, I have found nothing like this either on the net or in the
Oracle documentation.

Any ideas?

Thx
Jul 19 '05 #1
1 8598
Agoston Bejo wrote:
The problem is as follows:
I have a stored proc. that returns a types.ref_cursor (where
types.ref_cursor is defined as 'ref cursor'):
PROCEDURE P(c OUT TYPES.ref_cursor);
The returned cursor has a bunch of columns, but I only need a few of them.
Anyway, with FETCH INTO I have to provide variables for every column. So I
have to do this:
var1 Type1;
var2 Type2;
...
FETCH c INTO var1, var2, ..., var20, ... var_n;
Where Type1, etc. ... are as specified in the documentation of P. (Since I
couldn't find a way to determine the record type of an arbitrary
ref_cursor.)
Is there a way to make this type of situation easier?
Some solutions came into my mind, such as:

1. I could declare a variable with the type c%ROWTYPE or something.
2. I could FETCH only the columns that I need, e.g.
FETCH c(col1, col5, col7) INTO var1, var5, var7;
3. I could somehow find a way for the procedure to return something other
than a ref_cursor which seems a nightmare to work with.

Unfortunately, I have found nothing like this either on the net or in the
Oracle documentation.

Any ideas?

Thx


The code has more problems than you may think:

select keyword
from v$reserved_words
where keyword like 'TY%';

Assuming Oralce 9i ... you don't say ... redefine as:

PROCDURE P (c OUT SYSREFCURSOR)

The solution to your problem can be found at:
http://www.psoug.org/reference/ref_cursors.html

--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: RR | last post by:
Hi, It appears that DB2 has an arbitrary restriction on the use of "fetch first N rows". I want to insert into a table from a large table query, but only insert the first N rows: insert...
4
by: db2admin | last post by:
hi, For the following SQL and plan ============================================================== SELECT Q1.PLCY_KY, Q1.PLCY_TRM_EFCTV_DT, Q1.ACNTG_BGN_DT, CASE WHEN ((Q1.PLCY_SRC_CD = '02')...
2
by: technocrat | last post by:
HOW CAN THIS BE DONE IN JAVA??? THIS IS POSITIONED UPDATE EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEE FOR UPDATE OF JOB; EXEC SQL OPEN C1;
0
by: vinidimple | last post by:
Hi i have a serious problem while i was working in Excel.I want to fetch columns from an excel worksheet and i need to compare it with an sql querry fields,so i tried to open an excle...
0
by: Greg Collins [Microsoft MVP] | last post by:
I have written and posted the following articles which will prove useful to some: Display Lists in Columns Horizontally Using One Cell per Column...
1
by: angappan | last post by:
to fetch a list of rows and columns from xml and to store it in array and how to count the dynamic rows and columns ........ Help with code and explanations .... Iam very new to xml...
1
by: Agoston Bejo | last post by:
The problem is as follows: I have a stored proc. that returns a types.ref_cursor (where types.ref_cursor is defined as 'ref cursor'): PROCEDURE P(c OUT TYPES.ref_cursor); The returned cursor has...
0
by: padmaneha | last post by:
Hi Thanks for your response but the query which you gave in my previous post doesnt work. It displays the same cnt for movie reviews and videoreviews Let me explain you in detail As I...
3
Soniad
by: Soniad | last post by:
Hello, In stored procedure, i am using query that fetches 2 columns record but single row. i have used cursor to fetch single record. is there any way to fetch the record and put it in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.