PLS-00452 - Subprogram 'xxx' violates its associated pragma 1

[kjnorman]

I can not seem to resolve the following problem. Compliling the following returns PLS-00452: Subprogram 'BLOCK_OFFSET' violates its associated pragma.

However, I can not see anything that would indicate that the function is writing to the database.

-- In Header
function block_offset(InDate in date, OutDate in date, InBlock in date, OutBlock in date) return number;
pragma restrict_references(block_offset, WNDS);

-- In Body
function block_offset(InDate in date, OutDate in date, InBlock in date, OutBlock in date) return number
is
begin
If (InBlock is null And OutBlock is null) or InBlock is null Then
return 0;
Else
If InBlock <= nvl(OutDate,sysdate) And nvl(OutBlock,sysdate) >= InDate Then
return least(nvl(OutBlock,sysdate), nvl(OutDate,sysdate)) - greatest(InBlock, InDate);
End If;
End If;
end;

Incidentally, if I remove the code from the function such as
function block_offset(InDate in date, OutDate in date, InBlock in date, OutBlock in date) return number
is
begin
return 0;
end;

It still fails, so I can only guess that it is something in the parameter declaration that is causing it to fail.

Any ideas? I am using Oracle 8.0.5 on NT (yeah I know its old...)

Thank you, Kerry.

 

[kjnorman]

Don't worry, I solved it!

The function is part of a larger package, that when called initializes some package variables, one of which writes to a package variable. The function itself is pure, but this initalization call was not declared as such.

When I place pragma restrict_references against the initialization procedure that sets up the package variables everything else flowed through and compiled.

So, the error message was ambiguous and the real cause of the error was higher up in the code.

Kerry

 

[KenCunningham]

A star for posting back with the solution!