Home » SQL & PL/SQL » SQL & PL/SQL » PIPE_ROW() & pipelined function (merged)
PIPE_ROW() & pipelined function (merged) [message #323739] Thu, 29 May 2008 06:14 Go to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
Hi,

Can anyone pls explain this..
Re: PIPE ROW() [message #323859 is a reply to message #323739] Thu, 29 May 2008 18:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#i53120

Pipelined functions [message #323882 is a reply to message #323739] Thu, 29 May 2008 23:03 Go to previous messageGo to next message
nagaa
Messages: 33
Registered: August 2007
Location: chennai
Member
Hi,

My requirements is to read the file and insert inot three external table.
I used 3 object(BH1,BH2 and BH3) to insert the records..
But pipelined functions returns only one values..
Can u pls help how to overcome this..




CREATE OR REPLACE function sample3_tab_fnc (pi_row IN sys_refcursor)
return sample3_typ_tab PIPELINED AS
l_field1 varchar2(32000);

l_header varchar2(3);
l_org number(3);
l_log number(3);
l_acct varchar2(19);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
LOOP
FETCH pi_row INTO l_field1;
EXIT WHEN pi_row%NOTFOUND;
if l_field1 = 'BH1' then
PIPE ROW ( BH1_typ(l_header,l_org, l_logo,acct));
GOTO end_loop;
elsif l_field1 = 'BH2' then
PIPE ROW ( BH2_typ(header,org, logo,acct));
GOTO end_loop;
elsif l_field1 = 'BH3' then
PIPE ROW ( BH3_typ(header,org, logo,acct));
GOTO end_loop;
end if;
null;
END LOOP;
RETURN;
END;
Re: Pipelined functions [message #323888 is a reply to message #323882] Thu, 29 May 2008 23:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the use of piping rows from a ref cursor? The idea behind piping is to generate a set, somewhat similar as a ref cursor.
Also, what has the insert-story to do with your procedure?

Re: Pipelined functions [message #323908 is a reply to message #323882] Fri, 30 May 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: PIPE ROW() [message #323912 is a reply to message #323739] Fri, 30 May 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=pipe+row&tab_id=&format=ranked

Regards
Michel
Re: Pipelined functions [message #323957 is a reply to message #323882] Fri, 30 May 2008 02:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
My requirements is to read the file and insert inot three external table.


Well, I'm afraid you're doomed to failure.

External tables are Read Only

From the Docs
Quote:
external_table_clause

Use the external_table_clause to create an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside database.
Previous Topic: Error in email
Next Topic: Oracle Pipelined Table Function
Goto Forum:
  


Current Time: Thu Dec 08 06:36:02 CST 2016

Total time taken to generate the page: 0.08204 seconds