8.0.5 PRAGMA restrict_references problem

[PCStorm]

All of the following works without a problem on 8i (8.1.7). However, I need it to work in our 8.0.5 database.

Function Prototype in package header:

FUNCTION Return_Factor
( Date_One DATE,
Date_Two DATE,
Date_Compare DATE,
Compare_type VARCHAR2 )
RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES ( Return_Factor, WNDS );

Function Definition in package body:

CREATE OR REPLACE FUNCTION Return_Factor
( Date_One IN DATE,
Date_Two IN DATE,
Date_Compare IN DATE,
Compare_type IN VARCHAR2 )
RETURN NUMBER
AS
gl_Between VARCHAR(20) := 'BETWEEN';
gl_Less VARCHAR(20) := '<';
gl_Equal VARCHAR(20) := '=';
gl_Greater VARCHAR(20) := '>';
gl_LessEqual VARCHAR(20) := '<=';
gl_GreaterEqual VARCHAR(20) := '>=';
BEGIN
IF Compare_type = gl_Between AND Date_Compare BETWEEN Date_one AND Date_two THEN
RETURN 1;
ELSIF Compare_type = gl_Less AND Date_Compare < Date_one THEN
RETURN 1;
ELSIF Compare_type = gl_Equal AND Date_Compare = Date_one THEN
RETURN 1;
ELSIF Compare_type = gl_Greater AND Date_Compare > Date_one THEN
RETURN 1;
ELSIF Compare_type = gl_LessEqual AND Date_Compare <= Date_one THEN
RETURN 1;
ELSIF Compare_type = gl_GreaterEqual AND Date_Compare >= Date_one THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

As you can see there is no updating of the database, it merely examines the variables and returns a 0/1. When I run this code calling the function to update the database it gives the error that it violates its associated pragma.

When I take the function out of the package it compiles and runs but does not do what it is supposed to.

When I run in 8i it works perfectly, even without the pragma. In 8.0.5 it will not compile without the pragma.

You guys are GREAT! I have always found everyone to be a wealth of information. Thanks.

 

[karluk]

Is there any reason you need Return_Factor to be a function? I think your problems would disappear if you changed it to be a stored procedure. Oracle 8.0.5 is probably worried about allowing database updates if you use your function in a select statement, which is supposed to be read only.

 

[PCStorm]

Response to above:

This is a function called by an SQL update statement. It is used in a calculation AS (A + B - C) * Return_Factor. When return factor is 1 the calculation is as is. When the return Factor is 0 the result is 0.

The return_factor does not update the database. It does not even read it.

It is also NOW working fine as a stand-alone function outside of the package. It would be nice to have it in the package though.

 

[karluk]

Have you tried specifying both the WNDS and WNPS purity levels? I found some documentation that this makes a difference in how package functions can be called in SQL statements.

 

[sem]

As you use pragma, I suppose your function is within package. Does it contain any (initializing) code? The problem may be not in function itself, but rather in package code. In this case you need also a pragma for the package.

 

[PCStorm]

Response to Sem:

The function is in the same package as the entire set of procedures. The function code is represented above in its entirety. Nothing left out. No initialization.

Response to Karluk:

Same problem when using WNPS, I had also tried RNDS.

 

[sem]

I mean package code - the code between BEGIN end END of package body. Example:

create or replace package dummy as
function echo(p in varchar2) return varchar2;
PRAGMA RESTRICT_REFERENCES ( echo, WNDS);
end;

create or replace package body dummy as
function echo(p in varchar2) return varchar2 is
begin
return p;
end;

/*This is package initialization code. It violates function's pragma as during the first invocation of dummy.echo data will be changed by this block
*/
begin
delete from emp;
end;

 

[PCStorm]

Sem: Misunderstood. Anyway there is nothing like that in the package body. The function is encapsulated in the same package as the calling procedures.

The package body only sets up some private global variables. I did try moving them all to the header, and it made no diff.

 

[sem]

My last message dissapeared, so I repeat it:


create or replace package dummy as
function echo(p in varchar2) return varchar2;
PRAGMA RESTRICT_REFERENCES ( echo, RNDS);
end;

create or replace package body dummy as
a date;
function echo(p in varchar2) return varchar2 is
begin
return p;
end;


--This violates echo's pragma
begin
a:=sysdate;
end;



In what part of insert statement do you use Return_Factor? Do you get error during compile or run time?