Not enough values selecting into a table value

Here is the code:

create type emp_high_sal_ot is object
(
    full_name varchar2(64),
    phone_number varchar(20),
    salary number(10,2)
);
create type emp_high_sal_nt is table of emp_high_sal_ot;

and the following function doesn’t compile:

create or replace function get_highest_paid_emps return emp_high_sal_nt 
AS
    rec emp_high_sal_nt;
    avg_sal NUMBER;
begin
    SELECT AVG(salary)
    INTO avg_sal
    FROM employees;
        
    SELECT last_name || ' ' || first_name, phone_number, salary
    INTO rec
    FROM employees
    WHERE salary > avg_sal;
    
    return rec;
end;

It says:

PL/SQL: ORA-00947: not enough values

Why is that?

Answer

Why is that?

You are trying to put 3 values (and multiple rows) into a single variable.

---

Use BULK COLLECT INTO and wrap the values in the object type:

create or replace function get_highest_paid_emps return emp_high_sal_nt 
AS
    rec emp_high_sal_nt;
    avg_sal NUMBER;
begin
    SELECT AVG(salary)
    INTO avg_sal
    FROM employees;
        
    SELECT emp_high_sal_ot(
             last_name || ' ' || first_name,
             phone_number,
             salary
           )
    BULK COLLECT INTO rec
    FROM employees
    WHERE salary > avg_sal;
    
    return rec;
end;
/

Given the sample data:

CREATE TABLE employees (first_name, last_name, phone_number, salary) AS
SELECT 'Alice', 'Abbot', '0123456', 100000 FROM DUAL UNION ALL
SELECT 'Betty', 'Baron', '1111111', 250000 FROM DUAL UNION ALL
SELECT 'Carol', 'Chris', '9876543', 300000 FROM DUAL;

Then:

SELECT * FROM TABLE(get_highest_paid_emps());

Outputs:

FULL_NAME	PHONE_NUMBER	SALARY
Baron Betty	1111111	250000
Chris Carol	9876543	300000

db<>fiddle here