Skip to Main Content
  • Questions

  • Pipelined functions, types, and objects.....

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mark.

Asked: August 12, 2004 - 11:46 am UTC

Last updated: September 28, 2011 - 12:42 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

As we are finally going to 9i with one of our larger databases, I'm
trying to get a better handle on some of the new features. The one
I'm working on now is pipelined functions. I need to understand them
so I can educate the developers.

Some of the points I'm confused about are:
- When to use what datatype?
- What needs to be an object?
- What needs to be based on an object?
- Are there rules about when to implement different datatypes?

Perhaps it's just my lack of exposure, or lack of understanding,
but it seems to me there are different datatypes that work in
different situations but are otherwise very similar is structure
and syntax. Are there guidelines to help understand which to
use in what circumstance?

I did some research on this site regarding pipelined functions,
and I'm tryring to come up with a simple(?) demo based on some
code I saw here, but I'm having a problem. Here's the code:
drop package my_pipe_pkg
/
drop type myTableType
/
drop type myScalarType
/
create or replace type myScalarType as object
(a number,
b number
)
/

create or replace type myTableType as table of myScalarType
/

create or replace package my_pipe_pkg as
type doc_id_list_type is table of varchar2(15) index by binary_integer;
function pipe_func(doc_id_tab doc_id_list_type) return myTableType PIPELINED;
end;
/

create or replace package body my_pipe_pkg as
function pipe_func(doc_id_tab doc_id_list_type) return myTableType
PIPELINED
is
begin
for i in doc_id_tab.first .. doc_id_tab.last
loop
pipe row(myScalarType(i, doc_id_tab(i)));
end loop;
return;
end;
end;
/

declare
doc_id_list my_pipe_pkg.doc_id_list_type;
begin
doc_id_list(1) := '100';
doc_id_list(2) := '99';
doc_id_list(3) := '98';
doc_id_list(4) := '97';
doc_id_list(5) := '96';
doc_id_list(6) := '95';
doc_id_list(7) := '94';
doc_id_list(8) := '93';
doc_id_list(9) := '92';
doc_id_list(10) := '91';

for x in(select * from table(my_pipe_pkg.pipe_func(doc_id_list))) loop
begin
dbms_output.put_line(x.a||'--'||x.b);
end;
end loop;
end;
/


The package seems to be defined correctly (at least it compiles),
but the anon PL/SQL block encounters:

for x in(select * from table(my_pipe_pkg.pipe_func(doc_id_list))) loop
*
ERROR at line 15:
ORA-06550: line 15, column 54:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 44:
PLS-00306: wrong number or types of arguments in call to 'PIPE_FUNC'
ORA-06550: line 15, column 32:
PL/SQL: ORA-00904: "MY_PIPE_PKG"."PIPE_FUNC": invalid identifier
ORA-06550: line 15, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 29:
PLS-00364: loop index variable 'X' use is invalid
ORA-06550: line 17, column 8:
PL/SQL: Statement ignored


Now, the goal here, is to show a simple example of how to replace
some existing functionality in 8i, where, given an array of numbers,
I need to process them in a particular order. Currently, in 8i,
we use a global temporary table to achieve this. I'd like a pipelined
function that I need to pass an array (in the real world, it'll pass
from a Pro*C app, but for demo purposes, passing an arbitrary list
from an anonymous PL/SQL block is fine) and get out the list of
numbers w/ another column that defines the order of the elements in
the array.

Ultimately, I want to do:
select * from table(my_pipe_func(list_of_ids));
and get:
1 100
2 99
3 85
4 44
5 66
6 33
7 93

given that the input, list_of_ids is
list_of_ids(1) := 100;
list_of_ids(2) := 99;
list_of_ids(3) := 85;
list_of_ids(4) := 44;
list_of_ids(5) := 66;
list_of_ids(6) := 33;
list_of_ids(7) := 93;


Can you help?

Thanks,

-Mark

and Tom said...

the answer is sort of simple really.

In order for something to be "bound" into SQL, for a datatype to be used in SQL, it must either be one of the hard coded BASE TYPES (number, date, varchar2, etc...) or a SQL type created via CREATE TYPE.


your doc_id_list doesn't fit this, doc_id_list is a PLSQL type and PLSQL types are *only* visable to PLSQL.


ops$tkyte@ORA9IR2> create or replace type doc_id_list_type as table of varchar2(15)
2 /

Type created.

ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package my_pipe_pkg
2 as
3 function pipe_func(doc_id_tab doc_id_list_type) return myTableType PIPELINED;
4 end;
5 /

Package created.

ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body my_pipe_pkg
2 as
3 function pipe_func(doc_id_tab doc_id_list_type) return myTableType
4 PIPELINED
5 is
6 begin
7 for i in doc_id_tab.first .. doc_id_tab.last
8 loop
9 pipe row(myScalarType(i, doc_id_tab(i)));
10 end loop;
11 return;
12 end;
13 end;
14 /

Package body created.

ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 doc_id_list doc_id_list_type;
3 begin
4 doc_id_list := doc_id_list_type('100', '99', '98' );
5 for x in(select * from table(cast( my_pipe_pkg.pipe_func(doc_id_list) as myTableType )) ) loop
6 begin
7 dbms_output.put_line(x.a||'--'||x.b);
8 end;
9 end loop;
10 end;
11 /
1--100
2--99
3--98

PL/SQL procedure successfully completed.


an alternate implementation that is really easy to bind to (just bind a string) would be:


ops$tkyte@ORA9IR2> create or replace type str2tblType as table of varchar2(30)
2 /

Type created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType
2 PIPELINED
3 as
4 l_str long default p_str || p_delim;
5 l_n number;
6 begin
7 loop
8 l_n := instr( l_str, p_delim );
9 exit when (nvl(l_n,0) = 0);
10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
11 l_str := substr( l_str, l_n+1 );
12 end loop;
13 return;
14 end;
15 /

Function created.

ops$tkyte@ORA9IR2> select rownum, column_value from TABLE( cast(str2tbl('100,99,98') as str2tblType) );

ROWNUM COLUMN_VALUE
---------- ------------------------------
1 100
2 99
3 98



you can of course "to_number" the column_value.

Rating

  (8 ratings)

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

Comments

Most excellent!

Mark J. Bobak, August 12, 2004 - 1:18 pm UTC

Thanks for the correction!

Also, I'll think more about what you said with regard to SQL types.

Thanks again!

(Sometimes I just can't see the forest for the trees....;-))


Clarification please

A reader, December 11, 2006 - 6:25 am UTC

Hi Tom,
Continuing from the example above.
From my understanding, since the return type of the function is declared as a new SQL type, you don't really need to have the function defined as PIPELINED. Is there any (dis)advantage of then defining the function as PIPELINED? Does it make the function more efficient?

Thanks

Tom Kyte
December 11, 2006 - 8:08 am UTC

if the function is not pipelined - then

plsql will be used to fill a collection in it's entirety. If that means "one billion rows", that will be one billion rows in memory. The client will wait until the last row is produced before getting the first row.


if the function is pipelined - then

plsql will be used to "pipe" back as many rows as the client has requested (array fetch size), then plsql will "suspend" and client will get control - processing the array fetch size number of rows. When and if the client asks for the next batch of rows - plsql will start again and continue working. At no time do we need to have memory to hold one billion rows and the client gets the first row as fast as it can (and if you don't ask for the last row, we never have to actually produce it)

Perfect..

A reader, December 11, 2006 - 8:44 am UTC

Hi Tom,
Thanks for the quick answer.

Would you recommend using pipelined functions when creating Reports as well?


Thanks

Tom Kyte
December 11, 2006 - 9:42 am UTC

I'd use "regular queries"

pipelined functions should be rather few and far between

o utilities like dbms_xplan
o ETL functions (extract/transform/load) maybe

very special purpose. but yes, I would use a pipelined function over and above a function that returns a collection - but I would use NEITHER if I could avoid them and just use SQL

Excellent say about the function Pipelined...

Gowtham Sen, December 25, 2006 - 5:42 am UTC

Hi Tom,

Its very good explanation about fcunctionality of a function to be pipelined or not.

While I am working with pipelined function I am facing with an error since 2 days.
The error I am getting is

"PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'"

The steps I followed in creating the pipelined function are as follows.


SQL> drop function GEN_DATE_DIM;

Function dropped

SQL> drop type Date_Dim_Data_Table;

Type dropped

SQL> drop type Date_Dim_Data;

Type dropped

Create or replace type Date_Dim_Data is object
2 ( Dt DATE NULL,
3 Day_Num INTEGER NULL,
4 Day_Nm VARCHAR2(30) NULL,
5 Week_Num INTEGER NULL,
6 Mnth_Num INTEGER NULL,
7 Mnth_Yr_Num INTEGER NULL,
8 Mnth_Nm VARCHAR2(20) NULL,
9 Qtr_Yr_Num INTEGER NULL,
10 Yr_Num INTEGER NULL,
11 Fiscal_Yr_Num INTEGER NULL
12 );
SQL> /

Type created

SQL> create or replace type Date_Dim_Data_Table
2 is table of Date_Dim_Data;
SQL> /

Type created

SQL> create or replace function Gen_Date_Dim
2 (
3 startDate IN VARCHAR2, -- start date
4 numYears IN NUMBER -- number of years
5 )
6 return Date_Dim_Data_Table
7 pipelined
8 is
9
10 V_Dt Date_Dim.Dt%type:=NULL;
11 V_Day_Num Date_Dim.Day_Num%type := NULL;
12 V_Day_Nm Date_Dim.Day_Nm%type:= NULL;
13 V_Week_Num Date_Dim.Week_Num%type:= NULL;
14 V_Mnth_Num Date_Dim.Mnth_Num%type:= NULL;
15 V_Mnth_Yr_Num Date_Dim.Mnth_Yr_Num%type:= NULL;
16 V_Mnth_Nm Date_Dim.Mnth_Nm%type:= NULL;
17 V_Qtr_Yr_Num Date_Dim.Qtr_Yr_Num%type:= NULL;
18 V_Yr_Num Date_Dim.Yr_Num%type:= NULL;
19 V_Fiscal_Yr_Num Date_Dim.Fiscal_Yr_Num%type:= NULL;
20
21 V_Current_Dt Date_Dim.Dt%type:=NULL;
22
23 begin
24
25 loop
26
27 --Date Conversion.
28 V_Current_Dt := to_date(Startdate,'DD/MM/YYYY');
29 V_Current_Dt := V_Current_Dt - 1 ;
30
31 for V_Num_years in 1 .. NumYears
32 loop
33
34 -- The Next date is genearated using Start date.
35 V_Dt := V_Current_Dt + 1;
36
37 --Day Num :
38 V_Day_Num := wb_day_of_week(V_Dt);
39
40 --Day Name : It give the day name.
41 V_Day_Nm := wb_day_name(V_Dt);
42
43 --Week Num :
44 V_Week_Num :=wb_week_of_month(V_Dt);
45
46 --Month Number
47 V_Mnth_Num := wb_cal_month_of_year(V_Dt);
48
49 --Month Year Number ??????
50 V_Mnth_Yr_Num := NULL;
51
52 --Month Name
53 V_Mnth_Nm := wb_cal_month_name(V_Dt);
54
55 --Quarter Year Number
56 V_Qtr_Yr_Num := wb_cal_qtr(V_Dt);
57
58 --Yearr Number
59 V_Yr_Num := wb_cal_year(V_Dt);
60
61 --Fiscal Year Number ?????
62 V_Fiscal_Yr_Num := NULL;
63
64 V_Current_Dt := V_Dt;
65
66 pipe row (Date_Dim_Data_Table(V_Dt,V_Day_Num,V_Day_Nm,V_Week_Num,V_Mnth_Num,NULL,V_Mnth_Nm,V_Qtr_Yr_Num,V_Yr_Num,NULL));
67
68
69 end loop;
70
71
72 end loop;
73
74 end Gen_Date_Dim;
75 /

Warning: Function created with compilation errors

SQL> show errors
Errors for FUNCTION OWB_WRHS.GEN_DATE_DIM:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/17 PLS-00306: wrong number or types of arguments in call to 'DATE_DIM_DATA_TABLE'
66/7 PL/SQL: Statement ignored


I verified the types of data, I am passing to the object and the number of objects i am passing.

Please help me. Please explain why is it so.

Thank you,
Regards

Gowtham Sen

Tom Kyte
December 25, 2006 - 7:42 am UTC

you have a date_dim_data element you are returning, NOT the _table type

You used the wrong type

Thank you Tom.

Gowtham Sen, December 25, 2006 - 6:07 am UTC

Hi Tom,

I got that. My mistake was here.

pipe row (Date_Dim_Data_Table(V_Dt,V_Day_Num,V_Day_Nm,V_Week_Num,V_Mnth_Num,NULL,V_Mnth_Nm,V_Qtr_Yr_Num,V_Yr_Num,NULL));

Instead of giving the object name, I have given the table object name.

Now I corrected as

pipe row (Date_Dim_Data(V_Dt,V_Day_Num,V_Day_Nm,V_Week_Num,V_Mnth_Num,NULL,V_Mnth_Nm,V_Qtr_Yr_Num,V_Yr_Num,NULL));

Now the fucntion created successfully.


Thank you,
Regards,

Gowtham Sen.

Can we define an object using table.column%type syntax

Naresh, September 28, 2007 - 2:54 pm UTC

Hello Tom,

Can we define object type using table_name.column%type?

Meaning I tried something like:

create or replace type myObject as object
(
m1 table1.column1%type
)
/

It gives as error - so I need to know if there i sany way to base it on table column data type. I want to use the object in a pipelined function ultimately.

Thanks,
Naresh
Tom Kyte
October 03, 2007 - 12:55 pm UTC

no, that is plsql syntax only.

Table function in Package

Rajeshwaran, Jeyabal, September 28, 2011 - 10:36 am UTC

Tom:

I am invoking a Table function defined in a package and getting this error.

Is that Table function defined in package are not suppored in SQL?

rajesh@ORA10GR2> create or replace package pkg_test
  2  as
  3     type emp_rec is record
  4     (p_empno number,p_ename varchar2(20));
  5     type emp_rec_ntt is table of emp_rec;
  6     function emp_fnc return emp_rec_ntt;
  7  end;
  8  /

Package created.

Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace package body pkg_test as
  2     function emp_fnc
  3     return emp_rec_ntt
  4     as
  5     l_result pkg_test.emp_rec_ntt := pkg_test.emp_rec_ntt();
  6     begin
  7     for x in (select * from emp)
  8     loop
  9             l_result.extend;
 10             l_result(l_result.count).p_empno := x.empno;
 11             l_result(l_result.count).p_ename := x.ename;
 12     end loop;
 13     return l_result;
 14     end;
 15
 16  end;
 17  /

Package body created.

Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table (pkg_test.emp_fnc);
select * from table (pkg_test.emp_fnc)
                     *
ERROR at line 1:
ORA-00902: invalid datatype


Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from table (cast(pkg_test.emp_fnc as pkg_test.emp_rec_ntt));
select * from table (cast(pkg_test.emp_fnc as pkg_test.emp_rec_ntt))
                                              *
ERROR at line 1:
ORA-00902: invalid datatype

Tom Kyte
September 28, 2011 - 12:42 pm UTC

You cannot reference a plsql type in SQL, SQL is "below" plsql - plsql can see sql types - sql does not see plsql types.


You would create EMP_REC as an object, and EMP_REC_NTT as a table of that - at the SQL level. Move those out of the package and use CREATE TYPE in SQL.

also, use a pipelined function

ops$tkyte%ORA11GR2> create type emp_rec as object
  2  ( empno number, ename varchar2(20) )
  3  /

Type created.

ops$tkyte%ORA11GR2> create type emp_rec_ntt as table of emp_rec;
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package pkg_test
  2  as
  3          function emp_fnc return emp_rec_ntt pipelined;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body pkg_test
  2  as
  3  
  4  function emp_fnc return emp_rec_ntt
  5  PIPELINED
  6  as
  7  begin
  8          for x in ( select emp_rec(empno, ename) data from scott.emp)
  9          loop
 10                  pipe row( x.data );
 11          end loop;
 12          return;
 13  end;
 14  
 15  end;
 16  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from table( pkg_test.emp_fnc );

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

helped alot

praneeth kumar, October 28, 2011 - 7:09 am UTC

hi tom , i got issue in creating reports , so i got good solution here.
simply super. thanks thanks thank u very much
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library