jmilz
March 2, 2015, 1:40am
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
IS
v_rec test_record;
BEGIN
FOR oRow IN (SELECT NAME FROM PERSONEN)
LOOP
v_rec.NAME := oRow.NAME;
END LOOP;
RETURN;
/* get the table */
SELECT * FROM TABLE();
The error i get is
PL/SQL: ORA-00904: “PROCEDURETEST1”: invalid identifier
What did I do wrong?
Jan
GTDG
March 2, 2015, 4:45am
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;
jmilz
March 3, 2015, 10:12pm
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
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 */
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?
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