How to call a pipelined function in TOAD editor

Toad for Oracle
1

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

2

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;

3

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?

4

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