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 Thu Jun 19 2003 - 14:21:22 CDT
