Reputation: 285
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;
/
Upvotes: 0
Views: 1112
Reputation: 143163
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>
Upvotes: 2