Re: Pragma Restrict_References Question.

Hi,

   Is there a pragma on BMQR_SQL.GET_DATASETS also?. I remember reading somewhere that pragma levels defince purity levels for a function/package and u cannot call a procedure which is less "pure", or something like that.
U can turn to metalink for further details.

Abhijith.

Bill Lucas wrote:
> 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 Thu Jun 19 2003 - 00:55:45 CDT