Calling DB functions from FORMS/REPORTS

From: Stuart Elvins <S.Elvins_at_btinternet.com>
Date: Tue, 8 Dec 1998 19:24:23 -0000
Message-ID: <74ju89$9i6$1_at_plutonium.btinternet.com>

Sure loads of people out there have come across this so here goes.

In a 7.3.2 database (i.e. PL.SQL v2) I have a the following function

    FUNCTION f1
    RETURN NUMBER
    IS
    BEGIN
      RETURN(1);
    END; Now I can reference this function from a data model query as

SELECT f1
FROM dual

If I have a formula column (probably applies to any PL/SQL block) I can reference the function in PL/SQL

FUNCTION CF_1FORMULA
RETURN NUMBER
IS
  l NUMBER := f1;
BEGIN
  IF f1 = 1
  THEN
.....

END; But if I try and reference the function in a cursor delaration I get 'ERROR 123 Function f1 MAY NOT BE USED IN SQL' and the Line and column given as part of the error indicate my function.

FUNCTION CF_1FORMULA
RETURN NUMBER
IS
  CURSOR c1 IS SELECT f1 FROM dual; IF f1 = 1 BEGIN
.....

END; Has been driving me ape today. Any solutions/suggestions must appreciated.

Ta

Stuart Received on Tue Dec 08 1998 - 20:24:23 CET