Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Pragma Restrict_References Question.
We have a project currently in development (our first Oracle project
actually).
In a package we are defining a function DATASET_PROPERTY and overloading it to accept a variety of inputs.
This function needs to be usable in a select statement so we assigned the appropriate Restrictions (WNPS, RNPS, WNDS). This seemed to cause a problem when the function had to populate a rowtype inside the function using a call to another package it says we are violating the Restrictions but if we populate the call write from the function it seems to accept the restrictions.
Here is an example (with a question at the bottom)...
This one works fine
/*****************************************************************/
FUNCTION Dataset_Property(vcDatasetID IN VARCHAR2, recEnum IN
BMQR_TYPES.ENUMERATIONS_RT) RETURN BOOLEAN AS
recDS BMQR_Types.DATASETS_RT;
BEGIN recDS.DatasetID := vcDatasetID;
--BMQR_SQL.GET_DATASETS(recDS); -- Using Actual select or else it says we are violating our pragma
SELECT *
INTO recDS
FROM tblDatasets
WHERE DatasetID = recDS.DatasetID;
RETURN(BITAND(recDS.Options,
recEnum.BitValue) = recEnum.BitValue);
END Dataset_Property;
/*****************************************************************/
This one below says we are violating the restriction
/*****************************************************************/
FUNCTION Dataset_Property(vcDatasetID IN VARCHAR2, recEnum IN
BMQR_TYPES.ENUMERATIONS_RT) RETURN BOOLEAN AS
recDS BMQR_Types.DATASETS_RT;
BEGIN recDS.DatasetID := vcDatasetID;
BMQR_SQL.GET_DATASETS(recDS); -- Using Actual select or else it says we are violatiung our pragma
RETURN(BITAND(recDS.Options,
recEnum.BitValue) = recEnum.BitValue);
END Dataset_Property;
/*****************************************************************/
For reference BMQR_SQL.Get_Datasets is a really simple procedure that does this...
/*****************************************************************/
PROCEDURE GET_DATASETS(rec IN OUT NOCOPY BMQR_TYPES.DATASETS_RT) IS
BEGIN
SELECT *
INTO rec
FROM tblDatasets
WHERE DatasetID = rec.DatasetID; -- <<tblDatasets>>;
END;
END Dataset_Property;
/*****************************************************************/
So it looks like if I take the code out of BMQR_SQL.GET_Datasets and use it inline in my function then there is no issues using the restrictions, however I have to duplicate this about 4 times through the various overloading. My Question is
Is it better to use the same select in all of the overloads of this function (and maybe risk missing one if I have to change it) or is it better to use the call to a BMQR_SQL.GET_Datasets and override the restrictions with a pragma Restrict_References DATASET_PROPERTY, TRUST?
Any thoughts on the best practice in this case would be appreciated.
Regards,
Bill Lucas
Received on Wed Jun 18 2003 - 11:44:37 CDT