PL/SQL Store return variable of a function in a Boolean variable

Question

So I have a function that accepts a student id and checks to see if the user exists. I created the function without error and I'm trying to store the return variable in a Boolean like this and print the result

SQL> DECLARE
 2  tf BOOLEAN;
 3  BEGIN
 4    EXECUTE tf := valid_stud(5)
 5    DBMS_OUTPUT.PUT_LINE(tf);
 6  END;
 7  /

But this is getting me an error.

ERROR at line 4: ORA-06550: line 4, column 9: PLS-00103: Encountered the symbol "TF" when expecting one of the following: := . ( @ % ; immediate The symbol "." was substituted for "TF" to continue.

My function is this

CREATE OR REPLACE FUNCTION valid_stud(v_student_id NUMBER) RETURN BOOLEAN
IS
v_id NUMBER;
BEGIN
  SELECT student_id
  INTO v_id
  FROM STUDENT
  WHERE student_id = v_student_id;
  IF SQL%FOUND THEN
      RETURN TRUE;
  END IF;

EXCEPTION
    WHEN no_data_found THEN RETURN FALSE;

END valid_stud;
/

boolean can only be used within pl/sql. it is not a SQL type. Also you need to remove EXECUTE and missing a semicolon on line 4; — OldProgrammer
– OldProgrammer, Commented Dec 3, 2018 at 18:07
EXECUTE is not a PL/SQL command. EXECUTE IMMEDIATE is, but you're not running dynamic SQL. — APC
– APC, Commented Dec 3, 2018 at 22:28

Littlefoot · Accepted Answer · 2018-12-03 18:37:51Z

Here's a demonstration which shows how to do that.

Sample table:

SQL> create table student (
  2    student_id   number
  3  );

Table created.

Function: I removed unnecessary check whether SELECT returned a value; if it did, fine. If not, it'll raise an exception.

SQL> create or replace function valid_stud (
  2    v_student_id number
  3  ) return boolean is
  4    v_id   number;
  5  begin
  6    select student_id into
  7      v_id
  8    from student
  9    where student_id = v_student_id;
 10
 11    return true;
 12  exception
 13    when no_data_found then
 14      return false;
 15  end valid_stud;
 16  /

Function created.

Testing:

SQL> set serveroutput on
SQL> declare
  2    tf boolean;
  3  begin
  4    tf := valid_stud(5);
  5    if tf then
  6       dbms_output.put_line('Student exists');
  7    else
  8       dbms_output.put_line('Student does not exist');
  9    end if;
 10  end;
 11  /
Student does not exist

PL/SQL procedure successfully completed.

SQL>

Collectives™ on Stack Overflow

PL/SQL Store return variable of a function in a Boolean variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related