Skip to Main Content
  • Questions
  • Non-pipelined table function with dynamic sql statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: December 08, 2016 - 12:32 pm UTC

Answered by: Chris Saxon - Last updated: November 23, 2020 - 6:13 am UTC

Category: Database - Version: Oracle 12c

Viewed 1000+ times

You Asked

Hello, Ask Tom Team.
Would you mind telling me how I can create table function without pipelined option (I think that it unnecessary, because I have not a big data in it)
in a package level using dynamic sql and table of records collection. I know that if I want to create this function (non-pipelined and not in a package) I must create schema types: object and nested table of this object(as example). I mean What can I use into cycle instead of "pipe row"?
example of pipelined function in a package:
create or replace package pkg as
type getrow is record(
a varchar2 (4000),
b varchar2 (4000)
);
-- table of records
type gettblrow is table of getrow;
--
function gettable return gettblrow;
/
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:='dynamic sql statement';
open c for sql_string;
loop
fetch c bulk collect into v_tbl;
for i in 1 ..v_tbl.count loop
pipe row (v_tbl(i)); --What I must to do there instead of pipe row?
end loop;
exit when c%NotFound;
end loop;
close c;
return;
end gettable;

and we said...

If you want to use a non-pipelined table function, then just return your data:

create or replace type tp as object (
  a varchar2 ( 10 ) ,
  b varchar2 ( 10 )   
);
/
create or replace type tp_arr as table of tp;
/

create or replace function f 
  return tp_arr as 
  retval tp_arr;
begin

  select tp(rownum, rownum) 
  bulk collect into retval
  from   dual
  connect by level <= 3;
  
  return retval;
end;
/

select * from table(f);

A          B        
---------- ----------
1          1         
2          2         
3          3   


But if you're using a pipelined table function, you have to use pipe row:

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 pipelined;
end pkg;
/
create or replace package body pkg
as
  function gettable return gettblrow pipelined is
    c sys_refcursor;
    v_tbl gettblrow;
    sql_string varchar2 ( 4000 ) ;
  begin
    sql_string:='select rownum, rownum from dual connect by level <= 3';
    open c for sql_string;
    loop
      fetch c bulk collect into v_tbl;
      for i in 1 ..v_tbl.count
      loop
        pipe row ( v_tbl ( i ) ) ; --What I must to do there instead of pipe row?
      end loop;
      exit
    when c%notfound;
    end loop;
  close c;
  return;
 end gettable;
end pkg;
/

select * from table(pkg.gettable);

A          B        
---------- ----------
1          1         
2          2         
3          3


If you want to know more about table functions, check the following links:

http://stevenfeuersteinonplsql.blogspot.co.uk/search/label/table%20function
https://oracle-base.com/articles/misc/pipelined-table-functions

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

December 08, 2016 - 5:33 pm UTC

Reviewer: A reader

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.

Chris Saxon

Followup  

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

December 09, 2016 - 6:29 pm UTC

Reviewer: A reader

thank you so much! It is very useful!
Connor McDonald

Followup  

December 10, 2016 - 1:39 am UTC

glad we could help

Type OBJECT and RECORD confusion

November 20, 2020 - 9:49 am UTC

Reviewer: Aakash from Switzerland, Zurich

Why record type used in the Non Pipe Lined version compared to Record type in PIPE LINED VERSION
Chris Saxon

Followup  

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

November 21, 2020 - 9:33 am UTC

Reviewer: Stew Ashton from France

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
Connor McDonald

Followup  

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

November 21, 2020 - 10:19 am UTC

Reviewer: Stew Ashton from France

I found confirmation in the documenation here:

https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/PIPELINED-clause.html#GUID-FA182210-C68D-4E03-85B9-A6C681099705

"Although the return type of a pipelined table function might appear to be a PL/SQL type, the database actually converts that PL/SQL type to a corresponding SQL user-defined type."

Regards,
Stew