# How to call a pipelined function in TOAD editor

**URL:** <https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [March 2, 2015, 1:40am UTC](https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728 "2015-03-02T01:40:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmilz](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jmilz](https://forums.toadworld.com/u/jmilz)\
**Post date:** [March 2, 2015, 1:40am UTC](https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728/1 "2015-03-02T01:40:37Z")

</div>

Hi

I try to debug a pipelined function in the editor window.

But I cannot figure out, how to call it.

When I try a normal function, it works:

DECLARE

returnValue VARCHAR2 (4000) := NULL;

FUNCTION procedureTest (parameter1 VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN parameter1;

END;

/\* Call the procedure \*/

BEGIN

returnValue := procedureTest (‘TEST’);

END;

But when I try to call a pipelined function (inside the same editor window) it does not work:

DECLARE

TYPE test\_record IS RECORD (NAME VARCHAR2:= ‘’);

TYPE test\_table IS TABLE OF test\_record;

FUNCTION procedureTest1 ()

RETURN test\_table  
PIPELINED

IS

v\_rec test\_record;

BEGIN

FOR oRow IN (SELECT NAME FROM PERSONEN)

LOOP

v\_rec.NAME := oRow.NAME;  
PIPE ROW (v\_rec);

END LOOP;

RETURN;  
END;

/\* get the table \*/

SELECT \* FROM TABLE();

The error i get is

PL/SQL: ORA-00904: “PROCEDURETEST1”: invalid identifier

What did I do wrong?

Jan

---

<div class="post-metadata">

**Author:** ![GTDG](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gtdg/32/140_2.png) [@GTDG](https://forums.toadworld.com/u/GTDG)\
**Post date:** [March 2, 2015, 4:45am UTC](https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728/2 "2015-03-02T04:45:14Z")

</div>

You can’t have empty parentheses in your function declaration

function procedureTest1

return test\_table

pipelined

is

v\_rec test\_record;

begin

for oRow in (select NAME

```
              from PERSONEN)

```

loop

```
  v_rec.NAME := oRow.NAME;

  pipe row (v_rec);

```

end loop;

return;

end;

---

<div class="post-metadata">

**Author:** ![jmilz](https://avatars.discourse-cdn.com/v4/letter/j/ecb155/32.png) [@jmilz](https://forums.toadworld.com/u/jmilz)\
**Post date:** [March 3, 2015, 10:12pm UTC](https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728/3 "2015-03-03T22:12:54Z")

</div>

Yes, that’s true. I stripped the code of all non-important details but I deleted to much.

Let’s say there is a parameter NR:

function getPersonnelName(PersNr NUMBER)

return test\_table  
pipelined

is

v\_rec test\_record;

begin

for oRow in (select NAME

```
              from PERSONNEL where NR = PersNr)

```

loop

```
  v_rec.NAME := oRow.NAME;

  pipe row (v_rec);

```

end loop;

return;

end;

Then I would like to call it inside the same TOAD editor window with:

/\* get the table \*/  
SELECT \* FROM TABLE(getPersonnelName(123));

But that does fail with

PL/SQL: ORA-00904: “getPersonnelName”: invalid identifier

Someone said that, as I’m using the ‘SQL engine’ when getting the table, the function is not visible to it, because it is not ‘exposed’ (as declared in the header of a package).

If I put the same function in a package, declaring the function in it’s header, it works.

May that be the problem?

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [March 4, 2015, 8:09pm UTC](https://forums.toadworld.com/t/how-to-call-a-pipelined-function-in-toad-editor/32728/4 "2015-03-04T20:09:57Z")

</div>

your pipeline call looks ok now and for your information I work with it in Toad very long and without problems at all.

> PL/SQL: ORA-00904: "getPersonnelName": invalid identifier

hm ... problem is that you do not synonym on that object (object is in another schema than caller) or you do not have execute grant (object is if in another schema again than caller)

Next what I would do is to make type declaration out of procedure and for "test\_table" type grant to public.

Hope this helps

Damir