plsql - I m trying to make pipelined table function call but i m getting error in package body. Can u please make it correct

Question

create or replace Type e_record AS Object(
        e_uid number,
        e_first_name varchar2(50) ,
        e_last_name varchar2(50),
        e_age number,
        e_department varchar2(50),
        e_designation varchar2(50),
        e_salary number
);

create or replace Type e_record_table IS table Of e_record; 



Create or replace package E_package
AS 
    
    function list_empDetails return e_record_table pipelined;

end E_package;
/

-------Package Body----------


Create or replace package body E_package 
AS

    Function list_empDetails return e_record_table pipelined
    IS
        e_ret e_record_table := e_record_table(null,null,null,null,null,null,null);
    Begin
        for x in(select e_uid,e_first_name,e_last_name,e_age,e_department,e_designation,e_salary into e_ret from Employee_details) Loop
        pipe row(e_ret);
        End Loop;
        return;
    end list_empDetails;
end E_package;
/

And the error i m getting is :

Error at line 8: PL/SQL: ORA-00947: not enough values
Error at line 9: PL/SQL: Statement ignored
Error at line 9: PLS-00382: expression is of wrong type
Error at line 8: PL/SQL: SQL Statement ignored

Answer 1

There are a couple of problems with your code.

Firstly, the not enough values error was because you are querying 7 columns and only providing one variable to put them in in the INTO clause. However, you don't use the INTO clause when looping over the results of a query that way, so the first thing you can do is to get rid of it. Your loop variable x will contain each row of data read from your Employee_details table, you don't need another variable for it.

The next thing to note is that you declared e_ret of type e_record_table. Your pipelined function returns e_record_table, but the type of each row you pipe out needs to be the type your table type contains, not the table type. The expression is of wrong type error is because you were attempting to pipe out a value of type e_record_table. Anyway, you're not selecting anything into e_ret any more, so you can just delete this local variable.

Your loop variable x contains each row read from the query. As written, it contains 7 values, so you could create an e_record row to pipe back using something like the following:

        pipe row(e_record(x.e_uid, x.e_first_name, x.e_surname, x.e_age, ...));

However, that makes quite a lot of typing since you are repeating the list of columns in your query. You can avoid the duplicated typing by creating the record in the query and then piping that out:

        for x in(select e_record(e_uid,e_first_name,e_last_name,e_age,e_department,e_designation,e_salary) as emp from Employee_details) Loop
            pipe row(x.emp);
        End Loop;

The full working function is below:

    Function list_empDetails return e_record_table pipelined
    IS
    Begin
        for x in(select e_record(e_uid,e_first_name,e_last_name,e_age,e_department,e_designation,e_salary) as emp from Employee_details) Loop
            pipe row(x.emp);
        End Loop;
        return;
    end list_empDetails;