PLS-00201 Error in Pipelined Function in PL/SQL

Question

I was trying to create a pipelined table function in PL/SQL but facing the below error. Is this an syntax error?

CREATE OR REPLACE FUNCTION FUNC_IDS(
    IDS_IN IN VARCHAR2
) RETURN IDS_T
    PIPELINED
IS

BEGIN
 
select * from dual;  
    return;

END func_ids;

---

Script Output:

Function FUNC_IDS compiled 

LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       PL/SQL: Compilation unit analysis terminated

3/10      PLS-00201: identifier 'IDS_T' must be declared
Errors: check compiler log

---

I missed to create the row and table types before creating the func. Have created them later as below and trying to create the function that gets input as a string of IDs and pipe out the individual IDs to another function.

CREATE TYPE TF_ROW AS OBJECT (ID NUMBER);

CREATE TYPE IDS_T IS TABLE  OF TF_ROW;

create or replace function func_ids (ids_in in varchar2) return ids_t pipelined is
  n_start      pls_integer := 1;
  n_end        pls_integer := 1;
--
begin
  
  loop
    -- find the first and next comma in string
    n_start := instr(ids_in, ',', n_start, 1);
    n_end   := instr(ids_in, ',', n_start, 2);
    --
    if (n_end <= 0) then
        exit;
    end if;

    -- get the string and pipe it back out
    pipe row (to_number(substr(ids_in, n_start+1, n_end - n_start - 1)));
    -- ready for next one
    n_start := n_end;
  end loop;
  return;
end func_ids;


Script Output:

Function FUNC_IDS compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
22/5      PL/SQL: Statement ignored
22/15     PLS-00382: expression is of wrong type
Errors: check compiler log

What's wrong with the expression here?pipe row (to_number(substr(ids_in, n_start+1, n_end - n_start - 1)));

Answer 1

As you were told, IDS_T isn't declared.

Here's an example which works. See how I did it, do it yourself with your data.

Types:

SQL> create or replace type t_row is object (empno number, ename varchar2 (20));
  2  /

Type created.

SQL> create or replace type t_tab is table of t_row;
  2  /

Type created.

Function:

SQL> create or replace function func_ids (ids_in in varchar2)
  2     return t_tab
  3     pipelined
  4  is
  5  begin
  6     for cur_r in (select empno, ename
  7                     from emp
  8                    where deptno = ids_in)
  9     loop
 10        pipe row (t_row (cur_r.empno, cur_r.ename));
 11     end loop;
 12
 13     return;
 14  end func_ids;
 15  /

Function created.

Testing:

SQL> select * from table(func_ids (20));

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7566 JONES
      7788 SCOTT
      7876 ADAMS
      7902 FORD

SQL>

---

How to use the result returned by FUNC_IDS? I'm doing it using code you posted as a comment (the FUNC_ENAME function); I marked what you did wrong.

SQL> CREATE OR REPLACE FUNCTION func_ename (ids t_tab)
  2     RETURN VARCHAR2
  3  IS
  4     l_tmp     VARCHAR2 (100);
  5     l_result  VARCHAR2 (400);
  6  BEGIN
  7     l_result := ';';
  8
  9     FOR i IN ids.FIRST .. ids.LAST
 10     LOOP
 11        SELECT l.ename
 12          INTO l_tmp
 13          FROM emp l
 14         WHERE l.empno = ids (i).empno;         --> this line was wrong
 15
 16        l_result := l_result || l_tmp || ';';
 17     END LOOP;
 18
 19     RETURN l_result;
 20  END;
 21  /

Function created.

SQL> SELECT func_ename (func_ids (10)) FROM DUAL;

FUNC_ENAME(FUNC_IDS(10))
------------------------------------------------------------------
;CLARK;KING;MILLER;

SQL>