This function declaration has apparently some compilation error but i can't find what's wrong.. Every thing seems fine The function select notes from the table and verify if they are all superior to 95, if so the function return true.

I tried to remplace the first '(' by ':=' and ')' as suggested but it didn't work as i was expecting..

CREATE OR REPLACE FUNCTION BonnePerformance(codeP in CHAR(12), codeS in INTEGER)
RETURN VARCHAR2 IS
estBon VARCHAR2;
aNote INTEGER;
CURSOR allNote IS 
SELECT note
FROM Inscription
WHERE codePermanent = codeP AND codeSession = codeS;
BEGIN 
OPEN allNote;
FETCH allNote INTO aNote;
WHILE allNote%FOUND LOOP
IF aNote > 95 THEN
estBon := 'TRUE';
ELSE 
estBon := 'FALSE';
END IF;
FETCH allNote INTO aNote;
END LOOP;
CLOSE allNote;
RETURN estBon;
END;
/

Here the error

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/40     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := ) , default varying character large
         The symbol ":=" was substituted for "(" to continue.

2 Answers

2
Littlefoot On Best Solutions

From my point of view, you're doing it wrong (regardless errors you got while compiling the function). Why? Because it seems that the function is supposed to return exactly one value. If that's so, why do you use a cursor (and the loop)? If there are two (or more) rows that satisfy the condition, you'll return only the last value fetched, so ... what's the point?

Therefore, I'd suggest something like this:

  • select that single value and return the result
  • if nothing's found, handle it properly, using EXCEPTION section
    • in other words, don't "hide" the fact that no rows satisfy the condition within the cursor loop. Don't be lazy. Let someone else, who will be maintaining your code some day, know what's going on. You aren't writing a mystery book but a function

Here's what I meant. Test case first:

SQL> create table inscription
  2    (codepermanent varchar2(10),
  3     codesession   int,
  4     note          number
  5    );

Table created.

SQL> insert into inscription
  2    select 'x', 1, 20 from dual union all
  3    select 'y', 2, 99 from dual;

2 rows created.

The function:

SQL> create or replace function bonneperformance
  2    (codep in varchar2, codes in int)
  3  return varchar2
  4  is
  5    estbon varchar2(10) := 'UNKNOWN';
  6  begin
  7    select case when note > 95 then 'TRUE'
  8                else 'FALSE'
  9           end
 10    into estbon
 11    from inscription
 12    where codepermanent = codep
 13      and codesession = codes;
 14
 15    return estbon;
 16  exception
 17    when no_data_found then
 18      return estbon;
 19  end;
 20  /

Function created.

Testing:

SQL> select bonneperformance('x', 1) result from dual;

RESULT
--------------------
FALSE

SQL> select bonneperformance('y', 2) result from dual;

RESULT
--------------------
TRUE

SQL> select bonneperformance('z', 3) result from dual;

RESULT
--------------------
UNKNOWN

SQL>
2
Barbaros Özhan On

Your code gives an error because the length (12) defined for a function's parameter, which's violated and should be removed as .. BonnePerformance(codeP in CHAR, codeS in INTEGER). By the way it's a good habit to use a string type variable always as variable length. So, convert CHAR to VARCHAR2.

Moreover you have an issue with defining estBon local variable as VARCHAR2 without length. This case, a string type parameter needs a length such as estBon VARCHAR2(100). The length depends on your needs.

The last issue is the second use of FETCH allNote INTO aNote; just before END LOOP;. You don't need to use that again. Since you already used just after opening the cursor allNote above. So, convert your whole code to ;

CREATE OR REPLACE FUNCTION BonnePerformance(codeP in VARCHAR2,
                                            codeS in INTEGER)
  RETURN VARCHAR2 IS
  estBon VARCHAR2(100);
  aNote  INTEGER;
  CURSOR allNote IS
    SELECT note
      FROM Inscription
     WHERE codePermanent = codeP
       AND codeSession = codeS;
BEGIN
  OPEN allNote;
  FETCH allNote
    INTO aNote;
  WHILE allNote%FOUND LOOP
    IF aNote > 95 THEN
      estBon := 'TRUE';
    ELSE
      estBon := 'FALSE';
    END IF;
  END LOOP;
  CLOSE allNote;
  RETURN estBon;
END;