I have this PL / SQL function that accepts the name of a student (f_name). The function then displays all of the information for the given student from a premade table called students. The table contains 5 columns, 2 number type, and 3 varchar2 type. If the name isn't found in the table an error message is returned. My code so far is
CREATE OR REPLACE FUNCTION studentName(
f_name IN VARCHAR2)
RETURN
IS
v_test students%rowtype;
CURSOR c1
IS
SELECT * FROM students WHERE first_name = f_name;
BEGIN
OPEN c1;
FETCH c1 INTO v_test;
IF c1%notfound THEN
v_test := NULL;
END IF;
CLOSE c1;
RETURN v_test;
END;
I keep getting:
PLS-00382: expression is of wrong type
I believe from my initial return varchar2 statement. How do I allow the return to accept both varchar2 type and number type?
RETURN varchar2
You need to return the rowtype, but you are returning a scalar. VARCHAR2
cannot hold a row, it can hold only a string value.
Modify it to:
RETURN students%rowtype;
Demo using standard EMP table:
SQL> CREATE OR REPLACE FUNCTION studentName(
2 f_name IN VARCHAR2)
3 RETURN emp%rowtype
4 IS
5 v_test emp%rowtype;
6 CURSOR c1
7 IS
8 SELECT * FROM emp WHERE ename = f_name;
9 BEGIN
10 OPEN c1;
11 FETCH c1 INTO v_test;
12 IF c1%notfound THEN
13 v_test := NULL;
14 END IF;
15 CLOSE c1;
16 RETURN v_test;
17 END;
18 /
Function created.
SQL> sho err
No errors.
NOTE : %ROWTYPE
implies PL/SQL record type and PL/SQL types are not known to SQL. So you won't be able to use the function directly in plain SQL. You need to use SQL object type. Else you will get:
ORA-06553: PLS-801: internal error [55018]
Workaround to use it in SQL:
SQL> create or replace
2 type student_obj_type
3 as object(
4 student_id number,
5 stu_name varchar2(20),
6 dept varchar2(20)
7 )
8 /
Type created.
Use student_obj_type instead of students%rowtype to use the function in SQL.