PLS-00653: When Overloaded Pipelined Function Is Created Or Executed (Doc ID 1371524.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Following error is raised when overloaded pipelined function is created or executed

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope


Example 1
SQL> CREATE OR REPLACE PACKAGE BODY pk1
2 AS
3 FUNCTION f1 (y IN VARCHAR2)
4 RETURN ref_cursor_type
5 IS
6 c1 ref_cursor_type;
7 BEGIN
8 OPEN c1 FOR SELECT * FROM DUAL;
9
10 RETURN c1;
11 END f1;
12
13 FUNCTION f1 (x IN VARCHAR2)
14 RETURN ntt_strings
15 PIPELINED
16 IS
17 BEGIN
18 NULL;
19 END f1;
20
21 PROCEDURE p1
22 IS
23 c2 ref_cursor_type;
24 BEGIN
25 c2 := f1 (y => 'a');
26 END p1;
27 END pk1;
28 /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY PK1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/12 PLS-00653: aggregate/table functions are not allowed in PL/SQL
scope

Example 2
CREATE OR REPLACE TYPE foo_tbl_t IS TABLE OF VARCHAR2(32);
/
show errors

CREATE OR REPLACE PACKAGE foo
IS
FUNCTION bar RETURN NUMBER;
FUNCTION bar(i_dummy IN NUMBER) RETURN foo_tbl_t PIPELINED;
END foo;
/
show errors;

CREATE OR REPLACE PACKAGE BODY foo
IS

FUNCTION bar(i_dummy IN NUMBER)
RETURN foo_tbl_t PIPELINED
IS
BEGIN
PIPE ROW('foo');
RETURN;
END bar;

FUNCTION bar RETURN NUMBER
IS
BEGIN
RETURN 1;
END bar;

END foo;
/
show errors;

DECLARE
n NUMBER;
BEGIN
n := foo.bar;
END;
/

ERROR at line 5:
ORA-6550: line 6, column 13:
PLS-653: aggregate/table functions are not allowed in PL/SQL scope

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms