Using a cursor that is not yet defined 1

[ecannizzo]

I am creating a procedure in a package that calls a procedure in a package and sends out a cursor. The cursor coming out is a join of many tables. I need to then loop thru the cursor, however when I try to declare a variable to the cursor%rowtype I get that the cursor is not defined yet. Is there a way around this? Thanks!!

Here is some code:

cursorByPosEmpGroup tCursor;

-- role_rec cursorByPosEmpGroup%rowtype; --error here. can't set it to a specific table because it is a join of many

BEGIN
--fill in cursor from package
PKG_ROLE_POLICIES.GETROLEPOLICYBYPOSCODEGROUP(trim(posCode), trim(empGroup), cursorByPosEmpGroup);



--LOOP
--FETCH cursorByPosEmpGroup INTO role_rec;
--EXIT WHEN cursorByPosEmpGroup%NOTFOUND;

--get role_no
--roleNo := role_rec.Role_No;

--END LOOP;

end;

 

[SantaMufasa]

Ecannizzo,

Is there anything preventing your using an implicit cursor ?:

BEGIN
    FOR X IN (SELECT <expression-list>
                FROM <multi-table table-list>
               WHERE <join criteria>) LOOP
        roleNo := x.Role_No;
        <et cetera>
    END LOOP;
END;
/

Let us know why this won't work.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ecannizzo]

I need to call the query that is in the package so we don't have duplicate code all over the place.

 

[SantaMufasa]

I need to call the query that is in the package so we don't have duplicate code all over the place.

Beside populating the cursor, what does the package do? If it is just populating the cursor, why not create a VIEW that your multi-table join defines? Then you don't have "duplicate code all over the place".


Without additional details, it's a bit difficult to respond specifically, but I see a couple of options for your defining the storage for the cursor data:[ul][li]Create a RECORD definition that explicitly specifies the expressions returning from the CURSOR. (But at that point, you are back to "having duplicate code all over the place".)[/li][li]Or, create a TABLE which you can use to define your %rowtype.[/li][/ul]Let us know if we're getting warmer.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ecannizzo]

That's what I was thinking, that there is no good way to do this.

Thanks for you help!!

 

[taupirho]

I think there is milaeage in Dave's view idea.

Just pre-create your view definition from your complex multi-table select - with the addition of a 1=0 in the where clause to ensure there is no overhead when running it.

e.g

create your_new_view as
select t1.stuff,t2.stuff t3.stuff
from t1,t2,t3 etc ....
where ...
and 1=0

Then just change you role_rec definition in your code from

role_rec cursorByPosEmpGroup%rowtype;

to

role_rec your_new_view%rowtype;





In order to understand recursion, you must first understand recursion.