Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect & Pipelined Table Function - Confusion Building
icon5.gif  Bulk Collect & Pipelined Table Function - Confusion Building [message #190197] Tue, 29 August 2006 12:05 Go to next message
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 255 times)

[Updated on: Tue, 29 August 2006 12:09]

Report message to a moderator

Re: Bulk Collect & Pipelined Table Function - Confusion Building [message #190333 is a reply to message #190197] Wed, 30 August 2006 04:22 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Does this help?

http://asktom.oracle.com/pls/ask/f?p=4950:8:10563083706961403508::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5918938803188

Br
Kim
Re: Bulk Collect & Pipelined Table Function - Confusion Building [message #190445 is a reply to message #190333] Wed, 30 August 2006 14:47 Go to previous messageGo to next message
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 Building [message #190499 is a reply to message #190445] Thu, 31 August 2006 01:57 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

The possible solutions depends on Your version of Oracle.

Unfortunately, I have not been able to see Your attachment, so I cannot really give You any straight answer. When I opns it, I get an "file does not exist" error ;-(

Sorry
Kim
icon12.gif  Re: Bulk Collect & Pipelined Table Function - Confusion Subsiding [message #190695 is a reply to message #190499] Thu, 31 August 2006 17:47 Go to previous message
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

Previous Topic: Storing Alphanumeric values
Next Topic: complex processing with collection on each row?
Goto Forum:
  


Current Time: Wed Dec 07 16:50:48 CST 2016

Total time taken to generate the page: 0.05699 seconds