Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Pragma Restrict_References Question.

Pragma Restrict_References Question.

From: Bill Lucas <Homebrew42_at_hotmail.com>
Date: Wed, 18 Jun 2003 16:44:37 GMT
Message-ID: <VF0Ia.12516$Jw6.4901525@news1.news.adelphia.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US