Home » SQL & PL/SQL » SQL & PL/SQL » Returning a ref cursor with updated contents of a cursor variable (Oracle 10)
Returning a ref cursor with updated contents of a cursor variable [message #359525] |
Mon, 17 November 2008 02:39  |
inseticida
Messages: 3 Registered: November 2008
|
Junior Member |
|
|
I've been searching the forums here, but I was unable to find what I really need (however, I found many partial solutions, but when trying to put it together, it never worked).
So let me describe what I have and what I want to:
1. I have a VIEW, say, V_XPTO, whose columns are queried in a way they match exactly a structure I want to return to callers.
2. I have a type, say, t_XPTO, defined as
TYPE t_XPTO IS REF CURSOR RETURN V_XPTO%ROWTYPE;
3. So I use those definitions to typify things everywhere:
PROCEDURE GetXPTO( ..., o_XPTO out MY_TYPES.t_XPTO )
(...)
open o_XPTO for
select * from V_XPTO ...
4. That is ok so far for "Get*" transactions. But for transactions of, say, "Init*" type I need to fill it "by hand" and then return it.
Something like that:
PROCEDURE InitXPTO( ..., o_XPTO out MY_TYPES.t_XPTO )
(...)
-- create an empty instance.
r_XPTO t_XPTO;
(...)
-- do inits in 2 ou 3 columns (of about 20-30 that remain null).
r_XPTO.ID = (...);
r_XPTO.Name = (...);
(...)
-- return the initialized structure.
open o_XPTO for
select * from r_XPTO ...
Of course the last piece of code will produce a syntax error. I tried many sintaxs and I fail in "converting" r_XPTO (that is a cursor variable) to o_XPTO (that is a REF cursor).
My workaround is to do a select like
open o_XPTO for
select r_XPTO.ID, r_XPTO.Name, ... from dual;
But it is not a very acceptable solution for obvious reasons (too much typing and too high probability to fail in maitaining code as new columns are added).
Can you help me with some hints I should follow?
|
|
|
|
|
|
Re: Returning a ref cursor with updated contents of a cursor variable [message #359550 is a reply to message #359544] |
Mon, 17 November 2008 03:56   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You will always have to specify values for each column if you want it to return non-null values.
Given that, the easiest and quickest solution is:OPEN o_xpto for select <val_1>,<val_2>,....,<val_n> from dual; ,
or, if you want to draw these valuesin from somewhere else, build a string containing the SQL and open the ref cursor for that.
If you're happy to have null values, you can just open your ref cursor like this:OPEN o_xpto for select * from v_xpto where 1=2;
|
|
|
|
Re: Returning a ref cursor with updated contents of a cursor variable [message #359568 is a reply to message #359556] |
Mon, 17 November 2008 05:54  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The problem is that in the case of the INSERT you are passing in a set of column values, which is exactly what INSERT needs,
With UPDATE, you are simply extracting a value from the record and using it in exactly the same way that you don't seem to want to with a SELECT.
A SELECT statement needs a table-type data source.
All you have is a single row of data.
You could create an object type with the same structure as your table, and then create a collection on this objectm, and then use this collection as the datasource for your query, but this has all of the problems of the SELECT FROM DUAL method, in that you will need to kep adding new columns in.
Of course, you are over looking the fact that your prefered method - being able to select from a row-type will STILL require you to mantain the code every time the columns change, as you'll need to maintain the population of the row-type record.
Quote: | Do you guarantee me that Oracle pl/sql doesn't provide any feature that allows a ROWTYPE to be "converted" as input to a REF CURSOR, or you are just talking about what you know from your experience in using it?
|
The question is meaningless - al I can ever talk about is what I know. Even if I knew everything about oracle, I could never be certain that I knew everything about it.
I can say that I know of nothing that allows a non-collection variable to be used as a datasource that would not give you as much or more of a problem maintaining your code as the solution of OPEN cur FOR SELECT <list of values> from DUAL;
The response is notat all incoherrent - a rowtype is just that - a singkle row od data. It is not a table, it is not a collection, it is not a datasource. Things that need a row of data (Like INSERTS) will enerally accept it as an input.
Things that return a row of data (Like SELECT, or Cursors) will return them.
The fact that you can;t use a row as a table is hardly inconsistent.
If you want to mak a rod for your own back, here's some sample code for the object based solution:create table test_020 (col_1 date, col_2 number);
create or replace type ty_r_test_020 as object (col_1 date, col_2 number);
/
create or replace type ty_test_020 as table of ty_r_test_020;
/
declare
type ty_c is ref cursor;
c ty_c;
r ty_r_Test_020;
t ty_test_020 := ty_test_020();
begin
r := ty_r_test_020(sysdate,3);
t.extend(1);
t(1) := r;
open c for select * from table(t);
end;
/
|
|
|
Goto Forum:
Current Time: Tue Feb 11 13:45:44 CST 2025
|