A reader, December 08, 2016 - 5:33 pm UTC
Thanks, Chris. Very useful, I am using these variants, but also question was about if can I create non-pipelined function in package without creating object type in schema level and without using pipe row (pipelined option)? Can I put into dynamic query result into may be nested table of records (but I can't understand how)? As example I can't create object type and use pipelined. I must keep all logic in some package.
December 09, 2016 - 3:48 pm UTC
You can do this in 12c:
create or replace package pkg as
type getrow is record (
a varchar2 ( 10 ) ,
b varchar2 ( 10 )
) ;
-- table of records
type gettblrow is table of getrow;
--
function gettable
return gettblrow ;
end pkg;
/
create or replace package body pkg
as
function gettable return gettblrow is
c sys_refcursor;
v_tbl gettblrow;
sql_string varchar2 ( 4000 ) ;
begin
sql_string:='select rownum, rownum from dual connect by level <= 3';
select rownum, rownum bulk collect into v_tbl from dual connect by level <= 3;
return v_tbl;
end gettable;
end pkg;
/
declare
v_tab pkg.gettblrow;
begin
v_tab := pkg.gettable;
for rws in (select * from table(v_tab))
loop
dbms_output.put_line('A: ' || rws.a || ' B:' || rws.b);
end loop;
end;
/
A: 1 B:1
A: 2 B:2
A: 3 B:3
https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1
A reader, December 09, 2016 - 6:29 pm UTC
thank you so much! It is very useful!
December 10, 2016 - 1:39 am UTC
glad we could help
Type OBJECT and RECORD confusion
Aakash, November 20, 2020 - 9:49 am UTC
Why record type used in the Non Pipe Lined version compared to Record type in PIPE LINED VERSION
November 20, 2020 - 10:05 am UTC
You have to use SQL objects and nested tables/varrays for table functions.
Pipelined table functions can use either SQL types or PL/SQL records/collections.
Re: Type OBJECT and RECORD confusion
Stew Ashton, November 21, 2020 - 9:33 am UTC
Chiming in on the question "Why record type used in the Non Pipe Lined version compared to Record type in PIPE LINED VERSION?":
There are differences between SQL statements executed from within PL/SQL and SQL statements addressed to the database from a "client".
Within PL/SQL, SQL statements can use PL/SQL variables and constants as bind variables, and we can BULK COLLECT INTO collections of records. Outside of PL/SQL, there is no SELECT INTO and we are not allowed to access even package constants.
What is new in 12c? The ability, only within PL/SQL, to access PL/SQL collections in a TABLE clause of a SELECT statement.
Outside of PL/SQL, there is nothing new here.
All SELECT statements submitted outside of PL/SQL must use what I call "SQL collections", which are based on "schema-level" collection types , also known as "standalone" collection types. The collection elements cannot be PL/SQL records.
It would appear that pipelined table functions are an exception. That is not true: when we compile packages containing pipelined table functions, Oracle creates implicit standalone types for us and then uses them under the covers. In earlier versions, these types were visible in the *_OBJECTS views. They appear to be hidden now, but I assume they still exist.
Bottom line: SQL statements executed from outside PL/SQL cannot depend on PL/SQL types; they must refer to standalone types. The only (apparent) exception is pipelined table functions, which can depend on package level types that Oracle automatically "translates" into standalone types.
Best regards,
Stew Ashton
November 23, 2020 - 6:13 am UTC
btw, I'd still recommend using explicit SQL types - video coming soon about on my channel.
Connor
Adding to previous review
Stew Ashton, November 21, 2020 - 10:19 am UTC