How can I pass a PL/SQL cursor record created from multiple tables?

Question

This is PL/SQL in a 12c database.

I need to be able to pass a cursor record to a function. The problem is that the cursor is from three different tables and one of them must use a * to select all of the (hundreds) of fields in that table.
It works if I select distinct fields (from a smaller test table) and it works if I use a select * from one table and no other fields (no other tables involved), but I can't find any way to make this work when selecting from three tables (examples only show two) and using a * (to select all fields) from one of the tables.

I've tried FETCH INTO and SELECT INTO with a pre-defined cursor. I've tried using a cursor record with a FOR myRecord IN (SELECT a.*, ...) and creating a matching object (since records can't be created at the schema level)

These work

myVarA a%ROWTYPE;
SELECT a.* INTO myVarA  
  FROM a;

SELECT a.myAfield, b.myBfield INTO myVarA, myVarB
  FROM a, b;

But I need this to work:

myVarA a%ROWTYPE;
SELECT a.*, b.myBfield  INTO myVarA, myVarB  
  FROM a, b;

Using a cursor record

myRecord        myObjectTypeWithAllFields;      -- ojbect, not record as record can't be declared at schema level

FOR myRecord IN (SELECT a.*, b.myBfield FROM a, b); 
newVar  := myFunction(myRecord);        -- this won't work

fails on the function call with PLS-00306: wrong number or types of arguments in call to myFunction;

The function will do a lot of grunt work that is similar in many packages, but there is also a large amount of unique work in each package, so I can't just process the entire cursor loop in the function. I really need to pass one row at a time to the function.

Is there a way to do this?

Answer 1

You can define a cursor in a package - whether or not you will actually use it - with the same select list:

create package p as
cursor c is select a.*, b.myBField
from a, b; -- but use proper join syntax
end;
/

Then define your function argument using that cursor's %rowtype:

create function myFunction(p_record p.c%rowtype) return ... as ...

Then your block will work:

FOR myRecord IN (SELECT a.*, b.myBfield FROM a, b) LOOP
  newVar  := myFunction(myRecord);
END LOOP;

db<>fiddle demo

The cursor for loop could then use the cursor defined in the package if you wanted it to.

---

Incidentally, in your object version, the variable declaration:

myRecord        myObjectTypeWithAllFields;

is redundant; the myRecord in FOR myRecord... is completely independent. So it doesn't even attempt to use the object type.