Re: WHY can't I call a function from within a formula column???

John wrote:

> Hello,
> I am trying to modify pl/sql code behind a formula column (reports 2.5.7.17)
> and get this message when I try to compile:
> Function 'GETJOB' may not be used in SQL
>
> The code for the formula column:
> function CF_toolsFormula return Number is
>
> v_var1 VARCHAR2(10);
> begin
> BEGIN
> SELECT a.field1,
> INTO v_var1
> FROM table1 a,
> table2 b
> WHERE a.id = b.id
> AND a.job = :job
> AND rsi_attr.getjob(a.id, a.f1) = 1 ;
> END;
> END;
>
> The only line I added was the last line of WHERE clause (call to the
> function). Can you really not reference a function in a package from a
> formula column? I believe I did this with version 6. Is there a workaround
> to accomplish this in version 2?

Make sure you have the pragma statement in the package spec:

package rsi_attr is ...
  function getjob ...
...
pragma restrict_references (getjob, WNDS, WNPS[, RNDS][, RNPS]); end;

This assures Oracle that the function will not modify the database. Of course, you can only do this if the function really does *not* modify the database....

Tomm Received on Sun Dec 30 2001 - 06:06:04 CET