Hello,
I have a pretty specific question, so I looked for previous q&a about the topic but hadn't been able to find any. Could be me that I'm not able to condense the concept in a small amount of words. If so, and if you find out my question is a duplicate, I apologize in advance.
Also, I must tell you I'm into PL/SQL since less than a month, and never touched SQL before last September, so I'm a true beginner.
By the way, I wrote a function in a package, and it's something like this:
-- my_package declaration up here, including the type "my_type" my function returns --
CREATE OR REPLACE PACKAGE BODY my_package AS
FUNCTION my_function RETURNS my_type
IS
-- various variables declared and initialised --
to_return my_type := my_type(0, 0, 0, 0, 0, 0);
CURSOR my_cursor IS
-- select statement --;
row_of_my_cursor my_cursor%ROWTYPE;
BEGIN
-- function body here, where I open my_cursor, fetch stuff from my_cursor into row_of_my_cursor, manipulate it, then I close my_cursor, fill to_return and then return it --
END my_function;
...
The fact is, the select statement I used to define my_cursor is very long, compared to all the rest of the function, and it decreases readability. So what I wanted to do is:
- create another function "cursor_function" which returns a SYS_REFCURSOR with exactly the same select statement as before
- define a SYS_REFCURSOR variable in my_function, say "my_sysref SYS_REFCURSOR := cursor_function;"
All works like a charm, except that this way I'm not able to get the rowtype of my_sysref, so I have to fetch into *list of variables, one for every column* instead of fetching into a single variable with the same rowtype of my_sysref. Also, I know I could manually define the rowtype variable and fetch into that one, but my curiosity is if it is possible to avoid this and get the structure of a row directly from the REF CURSOR (could be useful if I want a function that needs just a re-compilation to adapt to a change in the CURSOR, without manual tweeking).
I guess the problem comes up because now I'm managing a REF CURSOR, not a CURSOR anymore, but it still bugs me since in most of the rest of the code I'm managing the REF CURSOR exactly the same way I was managing the CURSOR before.
Thanks in advance for any help and suggestion.
EDIT:
Thanks everybody. Got a lot of insight.
- Solomon and gaverill got fastly to the core of the problem, which is some technical stuff I found out lately yesterday during work, while I was trying to tighten the scope of the things I was not getting right. Still, thanks a lot.
- Cookiemonster had been very valuable because he gave me a very practical advise, which is also difficult to find around, as it comes with experience and not as a "documentation thing". In fact, this morning I thought I was perfectly able to get the thing working as I needed without messing around with cursors, and his post confirms my approach was wrong, or at least "non-idiomatic", yesterday.
- rp0428 gave me a very complete answer about what Solomon hinted. Also, I must thank him particularly because he correctly pointed out I was not putting down the question correctly. I tried to be concise to give you all only the useful information, taking it out from the context, but doing so I omitted some stuff and, lost in this work, forgot to specify what I wanted to achieve and why.
- Lothar showed me a tool I didn't know about, and confirmed once more my question wasn't complete at all. Thank you very much.
After all this, I found out there are simpler and more elegant ways to do what I tried to do, and that I was trying to use SQL not-needed features (like the PIVOT) to format tables and stick to complicated queries to format my stuff, where PL/SQL was exactly the solution to avoid all this. I won't bother you with all the details, as it would take time to explain to you my project, not to add stuff to a question which doesn't exist anymore.
Again, thanks a lot to everyone.
Messaged modified by myself (the author) after receiving answers which clarified what I was doing wrong.