Re: Pragma Restrict_References Question.

The purity was the issue, but the BMQR_SQL.GET_Datasets was a procedure and not a function. So that is why it wouldn't let me compile it with the restrictions I wanted.

I still don't have an answer as to which (if either) way is preferred

I can move the selects into the functions and compile them with the restrictions I want or I can say TRUST and make the call to the Get.

I am currently leaning toward the TRUST but still not really sure which way I will go, and in all honesty I am waffling on the decision.

Nothing like not having years of experience with a product and trying to make the right decisions...

Thanks,
Bill

"Abhijith" <abhijith.kashyap_at_oracle.com> wrote in message news:3EF2EE2E.2060206_at_oracle.com...
> 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 - 12:33:45 CDT