PL/SQL Function error Encountered the symbol "(" when expecting one of the following-CodePudding

error in using pl/sql function in the blocks of pl/sql

i'm trying to replace a char with a varchar2 using a function

i want to replace 'M' char to 'Male' VARCHAR2 after extract the data from the table using cursor

this is my code

CREATE OR REPLACE FUNCTION change_gender(GENDER IN CHAR)
   RETURN VARCHAR2(6);
IS 
    new_gender VARCHAR2(6); 
    BEGIN
    IF (GENDER == 'M') THEN
    new_gender := 'Male'
    return new_gender;
    END;
DECLARE
current_student_address STUDENTS.address%type;
CURSOR students_cursor IS
SELECT ID,NAME,GENDER
FROM STUDENTS
WHERE ADDRESS = current_student_address;
students_row students_cursor%ROWTYPE;
BEGIN
current_student_address := 'Road102';
FOR students_row IN students_cursor LOOP
DBMS_OUTPUT.PUT_LINE(students_row.id || ' ' || students_row.name || ' ' || change_gender(students_row.gender));
END LOOP;
END;

and this is the error I'm receiving

Error at line 2: PLS-00103: Encountered the symbol "(" when expecting one of the following:

   . @ % ; is default authid as cluster order using external
   character deterministic parallel_enable pipelined aggregate
   result_cache accessible rewrite

CodePudding user response：

It's just =, not ==. Also, you're missing statement terminator (semi-colon), as well as end if. Return datatype can't have size.

As of the function's logic: what about other genders? What will function return then? It must return something (even if it were null).

Therefore, I'd suggest something like this:

Sample data:

SQL> select * from students;

        ID NAME   ADDRESS GENDER
---------- ------ ------- ----------
         1 Little Road102 M
         2 Foot   Road102 F

Function:

SQL> CREATE OR REPLACE FUNCTION change_gender(GENDER IN CHAR)
  2     RETURN VARCHAR2
  3  IS
  4      new_gender VARCHAR2(6);
  5  BEGIN
  6    RETURN case when gender = 'M' then 'Male'
  7                when gender = 'F' then 'Female'
  8                else 'Other'
  9           end;
 10  end;
 11  /

Function created.

Testing:

SQL> set serveroutput on
SQL> DECLARE
  2    current_student_address STUDENTS.address%type;
  3    CURSOR students_cursor IS
  4      SELECT ID,NAME,GENDER
  5      FROM STUDENTS
  6      WHERE ADDRESS = current_student_address;
  7    students_row students_cursor%ROWTYPE;
  8  BEGIN
  9    current_student_address := 'Road102';
 10    FOR students_row IN students_cursor LOOP
 11      DBMS_OUTPUT.PUT_LINE(students_row.id || ' ' || students_row.name || ' ' ||
 12      change_gender(students_row.gender));
 13    END LOOP;
 14  END;
 15  /
1 Little Male
2 Foot Female

PL/SQL procedure successfully completed.

SQL>