PLS-00231: Function may not be used in SQL

10
votes

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?

2
oracleplsql
What are you trying to test? You just want to call the function and print your arrow for each row it returned? (Which is once for that function as you've shown it) – Alex Poole
Yes, I just want to call the function and print your arrow for each row it returned – David Silva
Your function (testDC) is privately declared within your anonymous block, so any queries you run (including any queries in your block) will not be able to see it. This is one of those cases where we see the the separation between the SQL and PL/SQL contexts. – Jeffrey Kemp

2 Answers

13
votes

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.

4
votes

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