|
|
|
|
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   |
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 #332394 is a reply to message #332390] |
Tue, 08 July 2008 07:40   |
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   |
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 #332424 is a reply to message #332422] |
Tue, 08 July 2008 08:52   |
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 #332437 is a reply to message #332432] |
Tue, 08 July 2008 09:18   |
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 #332449 is a reply to message #332437] |
Tue, 08 July 2008 09:48   |
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 #332593 is a reply to message #332459] |
Wed, 09 July 2008 02:05   |
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));
|
|
|
|
|