PLS-00231: Function may not be used in SQL

Question

I want to test my pipelined function without creating a package. The following example is simplified:

DECLARE
    FUNCTION testDC RETURN NCOL PIPELINED IS
    BEGIN
        PIPE ROW(5);
    END;
BEGIN
    FOR cur IN (select * from table (testDC())) LOOP
        dbms_output.put_line('--> ');
    END LOOP;
END;

But I get this error:

ORA-06550: line 7, column 7: pls-00231: function TESTDC may not be used in SQL
ORA-06550: line 7, column 7: PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 7, column 7: PL/SQL: SQL Statement ignored

What is better way to test these function?

Answer 1

Create your pipelined function as a standalone procedure or package member. Then you can call it from your script.

Also ensure that the NCOL parameter you refer to is declared in a schema that can be accessed by the calling script.

Answer 2

You can't access a table function direct in PL/SQL - see the test case below. So as other pointed out you must define the table function as standalone or packaged.

 DECLARE
   res NUMBER; 
   FUNCTION testDC RETURN NCOL PIPELINED IS
     BEGIN
         PIPE ROW(5);
     END;

 BEGIN
         res := testDC();
         dbms_output.put_line('--> '||res);

 END;
 /


 ORA-06550: line 3, column 12:
 PLS-00653: aggregate/table functions are not allowed in PL/SQL scope