 Bulk Collect & Pipelined Table Function - Confusion Building [message #190197] |
Tue, 29 August 2006 12:05  |
adminme
Messages: 31
Registered: May 2006
|
Member |
|
|
Hi:
<file attached>
I'm attempting to populate a table type via bulk collect in a cursor; pass that type into a pipelined table function then return another table type from the function for which to query off of.
I am new and jumping all over the place with this code in my head.
Are separate types for In & OUT params required? (these types are of a similar structure).
Am I using an object type in the correct context here?
Guidance is much appreciated...
see attached.
-
Attachment: pipeFAQ.txt
(Size: 1.11KB, Downloaded 692 times)
[Updated on: Tue, 29 August 2006 12:09] Report message to a moderator |
|
|
|
|
|
| Re: Bulk Collect & Pipelined Table Function - Confusion Building [message #190445 is a reply to message #190333] |
Wed, 30 August 2006 14:47  |
adminme
Messages: 31
Registered: May 2006
|
Member |
|
|
Wow - long reading session but very helpful. I actually resolved the same memory error a few days ago after reading parts of this article.
I'm confused about the applicability of my table object. Is it needed to pipe data out of the function? Is any "holder" like array/object required to pass data out of the pipelined function?
Does Tom suggest that simply calling the function inherently returns the piped data. I believe this is correct - the function doesn't return a type. Can you confirm?
thanks for the link.
|
|
|
|
|
|
 Re: Bulk Collect & Pipelined Table Function - Confusion Subsiding [message #190695 is a reply to message #190499] |
Thu, 31 August 2006 17:47 |
adminme
Messages: 31
Registered: May 2006
|
Member |
|
|
Kim:
Yes-attachment needs to downloaded.
I'm actually launching the code from 10g and retrieving data from 8i. Don't know where the source data will ultimately reside.
Well, I have a working pipeline table function now (woo hoo)- check it out at bottom. FYI pipeing this data was very slow.
I'm stuck on the FORALL w/insert error ' PL/SQL: ORA-00947: not enough values '. There are same # of cols in source and target and data in cursor.
FORALL CODE Failing:
DECLARE
TYPE t_All_Tab_cols IS TABLE OF schema.table@host%ROWTYPE;
--INDEX BY BINARY_INTEGER;
v_TabCols t_All_Tab_Cols;
CURSOR c1 IS SELECT * FROM table(s)
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_TabCols limit 800;
DBMS_OUTPUT.PUT_LINE('Rowcount: '||c1%ROWCOUNT);
END LOOP;
FORALL i IN 1..200
INSERT INTO temp2 -- 74 columns in temp2.
VALUES (v_TabCols(i));
--EXIT WHEN c1%ROWCOUNT = 10000;
CLOSE c1;
END;
TABLE FUNCTION CODE: working
CREATE OR REPLACE TYPE t_MyObject
AS OBJECT
( FY NUMBER (4),
SEQ_NUM NUMBER (13),
GL_SEQ_NUM NUMBER (13))
/
CREATE OR REPLACE TYPE t_MyTable
IS TABLE OF t_MyObject
/
CREATE OR REPLACE PACKAGE MyPipePkg IS
FUNCTION MyEtl (p_cursor in sys_refcursor) RETURN t_MyTable PIPELINED;
END MyPipePkg;
/
CREATE OR REPLACE PACKAGE BODY MyPipePkg IS
FUNCTION MyEtl(
p_cursor in sys_refcursor)
RETURN t_MyTable
PIPELINED
AS
a_rec temp%rowtype;
begin
loop
fetch p_cursor into a_rec;
exit when (p_cursor%notfound);
pipe row( t_MyObject( a_rec.fy,a_rec.seq_num,a_rec.gl_seq_num));
end loop;
return;
END MyEtl;
END MyPipePkg;
/
[Updated on: Thu, 31 August 2006 17:56] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
