did the approach work?
Bill Lucas wrote:
> Thanks,
> Bill
>
> "abhijith" <abhijit_at_nomail.com> wrote in message
> news:3EF15061.5090704_at_nomail.com...
>
>>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 Fri Jun 20 2003 - 06:21:18 CDT
