Ref Cursor return by Package Function - how to use it.

[jjob]

I have a Database Package with the following definitions


type AffectedLocations_rec is RECORD
(
affectedStructureName STRUCTURES.STRUCTURE_NAME%TYPE ,
affectedFloorLevelDesc FLOOR_LEVEL_TYPE.FLOOR_LEVEL_TYPE_DESC%TYPE,
affectedFloorLevelNo ASSOC_HAZARD_AREA_LIVE.ASSOC_FLOOR_LEVEL_NO%TYPE,
affectedRoomZone ASSOC_HAZARD_AREA_LIVE.ASSOC_ROOM_ZONE%TYPE
);

type AffectedLocations_refcur is REF CURSOR return AffectedLocations_rec;


function getAffectedLocations(
i_historicLive varchar2,
i_hazardSerialNo varchar2
) return AffectedLocations_refcur;



I would really like to see the returned recordset in the Data Model as a list of fields, as though I had written something like

SELECT affectedStructureName, affectedFloorLevelDesc , 	   affectedFloorLevelNo , affectedRoomZone FROM

theTable.

The reason I'm struggling to do that is because the table (theTable) can change and it seems easier to use PL/SQL in the database to cope.

I do not seem to be able to call this function, from anywhere. When in the Data Model, If I try Ref Cursor, I'm provided with a wrapper function skeleton, and my ref cursor is then not recognised as a return type if I place it as the return type of the wrapper, if I try SQL I'm told that the function is not a SELECT.

Having read the only other posts on this topic and searched the net and read the help files AND a training course manual, I am no wiser as to how to call this function. I can see the package in the database area of object navigator, but have no idea how to call it or where. I have no problem calling these things from with PL/SQL procs and functions in the database, but am lost in the Reports.

Any help, even pointers to a reference source would be much appreciated.

 

[jjob]

A glimmer of light!

I think I may have a pointer - the following rather unclear chunk of demo code in the Oracle on-line docs, tidies up as shown below it

function q_portdescRefCurDS return port_cv.portdesc_refcur istemp_portdesc port_cv.portdesc_refcur;begin open temp_portdesc for select department_id, department_name from departments; return temp_portdesc;end;


function q_portdescRefCurDS return
port_cv.portdesc_refcur

istemp_portdesc port_cv.portdesc_refcur;

begin

open temp_portdesc for select department_id, department_name from departments;

return temp_portdesc;

end;

which I think can be translated for my instance to

function myReportFunction return My_Pkg.AffectedLocations_refcur thisFunctionsReturnCursor My_Pkg.AffectedLocations_refcur is

begin

/* The 'L' and 239 below are hard coded for testing */
return My_Pkg.getAffectedLocations('L',239);

end;


The only bad news so far is the DB is now down and I can't see what happens to the above. I will post any results once I think they may prove useful.


I may move my package function code into the reports again, so the function call here is replaced by the PL/SQL of the function currently stored in the package, if the above doesn't work.

 

[jjob]

Gave up, I'm sure it can be done, but I can't waste any more time playing - I've gone for a SQL answer within the report, which was simpler anyway, I just got sidetracked/tunnel vision on the PL/SQL route.

 

[sem]

You may create a view as an intermediate between your changing table structure and report

Regards, Dima

http://seminihin.narod.ru

 

[nagornyi]

Ref cursor is extremely powerful tool, but very badly explained in Oracle tutorials. Spend some time on this:

http://otn.oracle.com/products/reports/htdocs/getstart/docs/B10310_01/orbr_refcur.htm

and you will never regret. You can follow the example with other data, it does not matter.

 

[jjob]

Many thanks for the replies, I'll look at this again, but I agree with the claim that the explanations are not good.

regards

John