Geek Answers Handbook

PLS-00231: function cannot be used in SQL

I want to test my pipeline 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: row 7, column 7: PLS-00231: TESTDC function cannot be used in SQL
ORA-06550: row 7, column 7: PL / SQL: ORA-00904 :: invalid identifier
ORA-06550: row 7, column 7: PL / SQL: SQL expression ignored

What is better to check these functions?

+5
source share
2 answers

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

Also, make sure that the NCOL parameter you are referencing is declared in the schema that the calling script can access.

+6
source

You cannot access the table function directly in PL / SQL - see the test case below. As others have indicated, 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
+2
source

More articles:


All Articles