Hello,
I am reading contradictory information regarding the return type that a table function must or can use.
First, I am studying from a book that says:
Pipelined function return datatypes:
The main restriction for pipelined functions is that the return type must be a standalone collection type that can be used in SQL - i.e. a VARRAY or nested table.
and then in the next sentence...
Specifically a pipelined function can return any of the following:
A standalone nested table or VARRAY, defined at schema level.
A nested table or VARRAY type which has been declared in a package.
This seems to me to contradict the first sentence quoted.
Now, prior to reading the above text I had just conducted my own test to see whether a packaged type would work because I thought I had read somewhere that it wouldn't, and it didn't (the test code and output is at the end of this question). So when I got to the text above after conducting my test, I was of course confused.
So I go to the PL/SQL Reference which says:
RETURN Data Type
The data type of the value that a pipelined table function returns must be a collection type defined either at schema level or inside a package (therefore, it cannot be an associative array type).
I have tried calling a function that returns a packaged VARRAY collection type in both SQL and inside PL/SQL (of course underneath it it's all SQL anyway) and neither works.
Now I am wondering is it that a TABLE function must use a schema type and a pipelined table function may use a packaged type? I see I created and called a table function but Oracle's examples show creation and use of a pipelined table function.
Edit: I should add that I have just read in SF's book on p609 the following sentence in the context of *table* functions: "This nested table type must be defined as a schema-level element because the SQL engine must be able to resolve a reference to a collection of this type."
So it is beginning to look like table functions must return a schema type and pipelined table functions, perhaps because they don't actually return a collection, rather they return a rowsource, can use either schema types or package types. Is that correct?
Can anybody clarify this for me please?
Thank you in advance,
J
CREATE OR REPLACE PACKAGE PKGP28M IS
type vat is varray(5) of number;
END;
/
SHOW ERRORS
create or replace type vat is varray(5) of number;
/
show errors
create or replace function tabfunc1 return pkgp28m.vat as
numtab pkgp28m.vat:=pkgp28m.vat();
begin
numtab.extend(5);
for i in 1..5 loop
numtab(i):=trunc(dbms_random.value(1,5));
end loop;
return numtab;
end;
/
show errors
create or replace function tabfunc2 return vat as
numtab vat:=vat();
begin
numtab.extend(5);
for i in 1..5 loop
numtab(i):=trunc(dbms_random.value(1,5));
end loop;
return numtab;
end;
/
show errors
exec dbms_output.put_line('calling tabfunc1 (returns packaged type):');
select * from table(tabfunc1)
/
exec dbms_output.put_line('calling tabfunc2 (returns schema type):');
select * from table(tabfunc2)
/
declare
rc sys_refcursor;
v number;
begin
dbms_output.put_line('In anonymous block1 - open rc for select from table(tabfunc1) (returns packaged type):');
open rc for select column_value from table(tabfunc1);
loop
fetch rc into v;
exit when rc%notfound;
dbms_output.put_line('>' || to_char(v));
end loop;
close rc;
end;
/
declare
rc sys_refcursor;
v number;
begin
dbms_output.put_line('In anonymous block2 - open rc for select from table(tabfunc2) (returns schema type):');
open rc for select column_value from table(tabfunc2);
loop
fetch rc into v;
exit when rc%notfound;
dbms_output.put_line('>' || to_char(v));
end loop;
close rc;
end;
/
SCOTT@ORCL> @C:\Users\J\Documents\SQL\test29.sql
Package created.
No errors.
Type created.
No errors.
Function created.
No errors.
Function created.
No errors.
calling tabfunc1 (returns packaged type):
PL/SQL procedure successfully completed.
select * from table(tabfunc1)
*
ERROR at line 1:
ORA-00902: invalid datatype
calling tabfunc2 (returns schema type):
PL/SQL procedure successfully completed.
COLUMN_VALUE
------------
1
4
1
1
3
In anonymous block1 - open rc for select from table(tabfunc1) (returns packaged type):
declare
*
ERROR at line 1:
ORA-00902: invalid datatype
ORA-06512: at line 6
In anonymous block2 - open rc for select from table(tabfunc2) (returns schema type):
>1
>2
>4
>2
>3
PL/SQL procedure successfully completed.
Message was edited by: Jason_942375