PL / SQL Function to return varchar2 / numbers

Question

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?

the type of v_test that you return `students%rowtype;` conflicts with your return type of `varchar2`. In fact to accept the return you´d need to, change the return type to `students%rowtype` — SomeJavaGuy, Nov 17 '15 at 07:01
`students%rowtype` is a type of *one variable*, what is the problem? — smnbbrv, Nov 17 '15 at 07:06

Lalit Kumar B · Accepted Answer · 2015-11-17T07:26:18.727

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.

I added another piece of information, hope it would help. – Lalit Kumar B Nov 17 '15 at 07:26 — Lalit Kumar B, Nov 17 '15 at 07:26

PL / SQL Function to return varchar2 / numbers

1 Answers1