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 Go to next message
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 #359530 is a reply to message #359525] Mon, 17 November 2008 02:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doing a select from dual is the most performant approach I can think of.
It's no more prone to failure than ano other approach that requires to to manually specify each value in the code.
Re: Returning a ref cursor with updated contents of a cursor variable [message #359532 is a reply to message #359525] Mon, 17 November 2008 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the columns are variable or not known at compile time use a weakly typed ref cursor like sys_refcursor.

Regards
Michel
icon6.gif  Re: Returning a ref cursor with updated contents of a cursor variable [message #359544 is a reply to message #359530] Mon, 17 November 2008 03:08 Go to previous messageGo to next message
inseticida
Messages: 3
Registered: November 2008
Junior Member
JRowBottom: thank you for your reply, but it fails to answer my question. I don't want the most performant; I just want to know how to "put" the r_XPTO into the o_XPTO without having to specificy each column.

Michel: thank you for your reply, but I need it to be strongly typified. Nonetheless, they can vary with time, say, a new column every week in some of the tables due to my customer's business changes, or a column that turns obsolete each month and must be removed to prevent junk accumulation). If I could just pass r_XPTO into o_XPTO, I will reduce the need of changes in the code, while maintaining it easily readable.


Bottom line: I just want an answer for what I stated in the topic. I'm aware of the issues with performance and/or strong/weak types but they're not related with the purposes I need to meet Cool
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 Go to previous messageGo to next message
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 #359556 is a reply to message #359550] Mon, 17 November 2008 04:45 Go to previous messageGo to next message
inseticida
Messages: 3
Registered: November 2008
Junior Member
So, what you are telling me is that when filling a REF cursor, there is no way one can use a ROWTYPE variable as input? Shocked

Seems to me that:
a) pl/sql lacks a feature.
a) either I'm not making me being understood correctly (because it's hard to me to believe it is in fact missing!).


I think that because I can do successfully:

r_XPTO T_XPTO%ROWTYPE;
(...)
insert into T_XPTO values r_XPTO;
(...)
update T_XPTO set row = r_XPTO where ID = r_XPTO.ID;
(...)


But I can't do:

r_XPTO T_XPTO%ROWTYPE;
(...)
open o_XPTO for select * from r_XPTO;
(...)


I know that in this reply I'm using T_XPTO (a table) and not V_XPTO (the view I used in last posts) but I think the concept is the same here, just avoiding to be syntaxtically incorrect.

JRowbottom: your suggestion of the null values is what I have currently to return a null structure (to be later populated in frontend). But the fact is that I need to initialize some of those values with database level information - that's why I declare a rowtype variable instead of directly returning the VIEW's contents with a "1=2" where clause.

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?

That seems quite incoherent to be able to work gracefully with ROWTYPE variables when inserting / updating tables, and then get stuck when trying to return the same information out the stored procedure recurring to intensively type all the code - somewhat spoils the main goal of using the ROWTYPE. Sad
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 Go to previous message
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;
/ 



Previous Topic: Oracle 7 Help
Next Topic: Need help in merging two database
Goto Forum:
  


Current Time: Sun Dec 04 23:01:52 CST 2016

Total time taken to generate the page: 0.15774 seconds