Home » SQL & PL/SQL » SQL & PL/SQL » Is "pipelining" feature available in 10G to select from PL/SQL table? (Oracle 10G)
Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332363] Tue, 08 July 2008 05:57 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
We are using PL/SQL tables.We are using the "pipelining" to select from Oracle PL/SQL tables. Is this changing with 10G?


Thanks in advance,

Regards,
Oli

[Updated on: Tue, 08 July 2008 06:00]

Report message to a moderator

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332364 is a reply to message #332363] Tue, 08 July 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use permanent type as pipelined function is intended to be used in SQL.

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332377 is a reply to message #332364] Tue, 08 July 2008 07:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
What is "permanent type"? Is this feature new to Oracle 10G.
Sorry, I am really not aware of it.



Thanks Michel.


Regards,
Oli

[Updated on: Tue, 08 July 2008 07:07]

Report message to a moderator

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332380 is a reply to message #332363] Tue, 08 July 2008 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To the best of my knowledge, Pipelined functions only work with collection types defined in SQL, not with types that are only declared in Pl/Sql.

If you've got an example to the contrary, I'd love to see it.
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332384 is a reply to message #332380] Tue, 08 July 2008 07:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 08 July 2008 07:12
To the best of my knowledge, Pipelined functions only work with collection types defined in SQL, not with types that are only declared in Pl/Sql.

If you've got an example to the contrary, I'd love to see it.


Thanks for the response. Here is the PL/SQL
The following code will create a Pipelined Table Function named generate_lines.

"pipelining" to select from Oracle PL/SQL tables

CREATE OR REPLACE FUNCTION generate_lines (cur_month_lines_ref
       IN ref_pkg.refcur_t)
RETURN ref_pkg.dst_ytd_lines pipelined
  IS
  r_month_costs       month_costs%ROWTYPE;
  r_ytd_costs         ytd_costs%ROWTYPE;

  TYPE dst_subcosts IS TABLE OF month_sub_costs%ROWTYPE;
  tab_subcosts      dst_subcosts := dst_subcosts();
  tab_subcosts_null dst_subcosts := dst_subcosts();
  -- For re-initialization

  CURSOR c_subtot (b_costno month_sub_costs.cost_no%TYPE)
  IS SELECT * FROM month_sub_costs WHERE cost_no = b_costno;

  t_is_data                     BOOLEAN ;
  t_diff_type                   BOOLEAN ;
  i_index                       PLS_INTEGER ;
  -- save_amount                   month_costs.amount%TYPE;

-- PL/SQL Block
BEGIN
  LOOP
    FETCH cur_month_lines_ref INTO r_month_costs ;
    EXIT WHEN cur_month_lines_ref%NOTFOUND ;

    -- Get data directly to be carried over from month_costs
       to ytd_costs here
    r_ytd_costs.cost_no         := r_month_costs.cost_no ;
    r_ytd_costs.quantity        := r_month_costs.quantity ;
    r_ytd_costs.type            := r_month_costs.type ;
    r_ytd_costs.amount          := r_month_costs.amount ;

    -- Initializations
    tab_subcosts        := tab_subcosts_null ;
    t_is_data           := FALSE ;
    t_diff_type         := FALSE ;
    i_index             := 0 ;

    FOR r_subtot IN c_subtot( r_month_costs.cost_no)
    LOOP
      t_is_data               := TRUE ;
      tab_subcosts.EXTEND ;
      i_index := tab_subcosts.LAST ;

      tab_subcosts(i_index).quantity  := r_subtot.quantity ;
      tab_subcosts(i_index).type      := r_subtot.type ;
      tab_subcosts(i_index).amount    := r_subtot.amount ;

      IF (((i_index - 1) > 0) AND ( tab_subcosts(i_index).type
         <> tab_subcosts(i_index - 1).type ))THEN
        t_diff_type    := TRUE ;
      END IF ;
    END LOOP ;

    i_index := 0 ;

    IF ( t_is_data = FALSE ) THEN
      PIPE ROW(r_ytd_costs) ;
    ELSE
      r_ytd_costs.quantity      := 0 ;
      r_ytd_costs.type          := NULL ;
      r_ytd_costs.amount        := 0 ;

      FOR i_index IN tab_subcosts.FIRST .. tab_subcosts.LAST
      LOOP
        r_ytd_costs.quantity    := r_ytd_costs.quantity
                                + tab_subcosts(i_index).quantity;
--        r_ytd_costs.type      := r_ytd_costs.type ;
        r_ytd_costs.amount      := r_ytd_costs.amount
                                + tab_subcosts(i_index).amount;
      END LOOP ;

      IF ( r_ytd_costs.amount = 0 ) THEN
        r_ytd_costs.type            := 'A' ;
      ELSE
        IF ( t_diff_type = TRUE) THEN
          r_ytd_costs.type          := 'B' ;
        ELSE
          r_ytd_costs.type          := 'C' ;
        END IF ;
      END IF ;
      PIPE ROW(r_ytd_costs) ;

    END IF ;

  END LOOP ;
  RETURN ;
END ;
/

[Updated on: Tue, 08 July 2008 07:28]

Report message to a moderator

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332386 is a reply to message #332364] Tue, 08 July 2008 07:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Tue, 08 July 2008 06:03
You have to use permanent type as pipelined function is intended to be used in SQL.

Regards
Michel




Michel Thanks.
But what is permanent type? Is it new features in oracle 10G. I am really not aware of it.
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332390 is a reply to message #332386] Tue, 08 July 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But what is permanent type?

I meant a type created with CREATE TYPE statement, what JRowbottom called a type defined in SQL.

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332394 is a reply to message #332390] Tue, 08 July 2008 07:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Tue, 08 July 2008 07:37
Quote:
But what is permanent type?

I meant a type created with CREATE TYPE statement, what JRowbottom called a type defined in SQL.

Regards
Michel



We are using PL/SQL tables .We are using the "pipelining" to select from Oracle PL/SQL tables in Oracle 9i database. Is this changing with 10G?
I'm not sure I understand. Does the existing code have to change?

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332400 is a reply to message #332384] Tue, 08 July 2008 08:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good grief - so you can.
I knew you could use a Pl/Sql collection type as output from a Pipelined function, but I'd thought that you needed a SQL type to CAST it to before you could use it in a SQL statement.
In 10g (lowest Db I've got to hand) that requirement is no longer there:
SQL> create or replace package pkg_type as
  2    type num_tab is table of number;
  3  end;
  4  /

Package created.

SQL> 
SQL> create or replace function num_pipeline (p_num in number) return pkg_type.num_tab pipelined is
  2  begin
  3    for i in 1..p_num loop
  4      pipe row(i);
  5    end loop;
  6  end;
  7  /

Function created.

SQL> 
SQL> select * from table(num_pipeline(5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5


That's today's learning experience taken care of. Thanks @Olivia

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332401 is a reply to message #332394] Tue, 08 July 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace package pkg is
  2    type mypkgtype is table of varchar2(100);
  3    function f return mypkgtype pipelined;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg is
  2   function f return mypkgtype pipelined
  3   is
  4   begin
  5    for i in 1..10 loop
  6     pipe row (chr(ascii('A')+i-1));
  7    end loop;
  8    return;
  9   end;
 10  end;
 11  /

Package body created.

SQL> select * from table(cast(pkg.f as pkg.mypkgtype));
select * from table(cast(pkg.f as pkg.mypkgtype))
                                  *
ERROR at line 1:
ORA-00902: invalid datatype

needs to be changed to:
SQL> create or replace type mytype is table of varchar2(100)
  2  /

Type created.

SQL> create or replace package pkg is
  2    function f return mytype pipelined;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg is
  2   function f return mytype pipelined
  3   is
  4   begin
  5    for i in 1..10 loop
  6     pipe row (chr(ascii('A')+i-1));
  7    end loop;
  8    return;
  9   end;
 10  end;
 11  /

Package body created.

SQL> select * from table(cast(pkg.f as mytype));
COLUMN_VALUE
-------------------------------------------------------------
A
B
C
D
E
F
G
H
I
J

10 rows selected.

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332406 is a reply to message #332400] Tue, 08 July 2008 08:14 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Razz Its indeed a great experience to learn under you peoples guidance.Learning is a never ending process..

Quote:

In 10g (lowest Db I've got to hand) that requirement is no longer there.



We are using the "pipelining" to select from Oracle PL/SQL tables in Oracle 9i database.Does the existing code have to be changed?

Whats will be the option?


Regards,
Oli

[Updated on: Tue, 08 July 2008 08:15]

Report message to a moderator

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332415 is a reply to message #332406] Tue, 08 July 2008 08:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Does the existing code have to be changed in 10G?
If yes, Whats will be the option?


Regards,
Oli
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332416 is a reply to message #332415] Tue, 08 July 2008 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Olivia wrote on Tue, 08 July 2008 06:29
Does the existing code have to be changed in 10G?
If yes, Whats will be the option?


Regards,
Oli



Why are you unwilling or incapable of testing this YOURSELF?
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332417 is a reply to message #332400] Tue, 08 July 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I knew you could use a Pl/Sql collection type as output from a Pipelined function, but I'd thought that you needed a SQL type to CAST it to before you could use it in a SQL statement.


But casting to a PL/SQL type is not allowed (as I showed in my example).
So in 9i, you have to use a SQL type.

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332419 is a reply to message #332416] Tue, 08 July 2008 08:37 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

@Michel
Because my database version is Oracle 9.2 not 10G.The reason why.
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332420 is a reply to message #332419] Tue, 08 July 2008 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand, this is why I answered for your version and not 10g.

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332422 is a reply to message #332419] Tue, 08 July 2008 08:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Olivia - does my example work on your 9i database?
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332423 is a reply to message #332422] Tue, 08 July 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works if you add a line "return;" in the function.
Oracle 9.2 always want a "return" in the function even if it is meaningless in pipelined function:
SQL> select * from table(num_pipeline(5));
ERROR:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "####.NUM_PIPELINE", line 3

Regards
Michel
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332424 is a reply to message #332422] Tue, 08 July 2008 08:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 08 July 2008 08:40
@Olivia - does my example work on your 9i database?


SQL> create or replace function num_pipeline (p_num in number) return pkg_type.num_tab pipelined is
  2      begin
  3       for i in 1..p_num loop
  4        pipe row(i);
  5        end loop;
  6     end;
  7  /

Function created.

SQL> select * from table(num_pipeline(5));
ERROR:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "SXV1.NUM_PIPELINE", line 3



That means code need to be changed (say we are defining SQL type in Oracle 10G)in 10G? Cant we define SQL type in 10G?

I want to know whether the below code as provided by Michel need to be changed in Oracle 10G



SQL> create or replace type mytype is table of varchar2(100)
  2  /

Type created.

SQL> create or replace package pkg is
  2    function f return mytype pipelined;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg is
  2   function f return mytype pipelined
  3   is
  4   begin
  5    for i in 1..10 loop
  6     pipe row (chr(ascii('A')+i-1));
  7    end loop;
  8    return;
  9   end;
 10  end;
 11  /

Package body created.

SQL> select * from table(cast(pkg.f as mytype));
COLUMN_VALUE
-------------------------------------------------------------
A
B
C
D
E
F
G
H
I
J

10 rows selected.



I am asking whether the above code will run in 10G or not without making any changes?
My initial query was [b]We are using the "pipelining" to select from Oracle PL/SQL tables (in Oracle 9i].Does the code need to be changed or it will run successfully? [b]
I am not expecting the features in 10g should be in 9i.I want to know whether above code(provided by Michel) will run without making any changes in 10G?

Jrowbottom, you have written your code compatible with 10G.And you were asking whether your code runs successfully or not.Can I ask you whether the code provided by Michel (for 9i) works without any error or not? I have no 10G environment to check.Hope you would understand!

Thanks to you in advance!


Regards,
Oli
Regards,
Oli

[Updated on: Tue, 08 July 2008 09:11]

Report message to a moderator

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332430 is a reply to message #332424] Tue, 08 July 2008 09:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Mea Culpa.

Could you try that again, with this correction to the function:
SQL> create or replace function num_pipeline (p_num in number) return pkg_type.num_tab pipelined is
begin
  for i in 1..p_num loop
    pipe row(i);
  end loop;
  return;
end;
/
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332432 is a reply to message #332424] Tue, 08 July 2008 09:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Mea Culpa.

Could you try that again, with this correction to the function:
SQL> create or replace function num_pipeline (p_num in number) return pkg_type.num_tab pipelined is
begin
  for i in 1..p_num loop
    pipe row(i);
  end loop;
  return;
end;
/


Michels code should work just fine in 10g as it is.
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332437 is a reply to message #332432] Tue, 08 July 2008 09:18 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
My question was all about whether pipelining" to select from Oracle PL/SQL tables(now we are doing in Oracle 9i) is possible without making any changes in 10G or not? Did you get what I mean?
I did not wanted to know the differnece but to know whether the exiting features in oracle 9i are available in 10G or not!

I was not interested whether Oracle 10G features works in Oracle 9i or not.

We are going to upgrade to Oracle10g.So, wanted to know if "pipelining" to select from Oracle PL/SQL tables will be possible without any changes in oracle10G.


Regards,
Oli

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332446 is a reply to message #332437] Tue, 08 July 2008 09:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your existing code should work just fine - Oracle work pretty hard to ensure backwards compatability.

Of course, you'd spot any problems when you did an upgrade on a test database first.....

Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332449 is a reply to message #332437] Tue, 08 July 2008 09:48 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I didnt have 10G environment to execute Michels code.So,I requested you to run.I hope that you wont mind for that.I need to verify that.You said "Your existing code should work just fine " (though thats not mine.Its Michel's example I am talking about).

Does should indicates that its confirmed? Need your help!

I didn't really ask whether any existing code in 10G works in Oracle 9i.Rather I asked whether written code in Oracle 9i will work fine in 10G. Literally, a Big difference is there in the query.


Thanks,
Oli
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332453 is a reply to message #332449] Tue, 08 July 2008 10:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Your existing code should work just fine - Oracle work pretty hard to ensure backwards compatability.
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332459 is a reply to message #332453] Tue, 08 July 2008 10:18 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks to you all for giving your time on this issue.
I owe Jrowbottom and Michel BIG thanks


Regards,
Oli
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332593 is a reply to message #332459] Wed, 09 July 2008 02:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For the interested people in the audience, it seems that SQL types and CAST are not needed for this at all in 9i. I found a 9i Db and tested this on it, and it all worked:
create or replace package pkg_type as
  type num_rec is record (col_1  number, col_2 number);
  type num_tab is table of number;
  type num_rec_Tab is table of num_rec;
end;
/

create or replace function num_pipeline (p_num in number) return pkg_type.num_rec_tab pipelined is
  r_num_rec   pkg_Type.num_rec;  
begin
  for i in 1..p_num loop
    r_num_rec.col_1 := i;
    r_num_rec.col_2 := i+1;
    pipe row(r_num_rec);
  end loop;
  return;
end;
/

select * from table(num_pipeline(5));
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332598 is a reply to message #332593] Wed, 09 July 2008 02:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks @JRowbottom
Re: Is "pipelining" feature available in 10G to select from PL/SQL table? [message #332689 is a reply to message #332598] Wed, 09 July 2008 06:40 Go to previous message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi all

Today i have learned a new topic..
Thank you very much
Previous Topic: Conditional statement within Where clause subquery
Next Topic: Insert Records
Goto Forum:
  


Current Time: Fri Dec 09 17:22:19 CST 2016

Total time taken to generate the page: 0.26800 seconds