Home » SQL & PL/SQL » SQL & PL/SQL » Help in Pipelined function to extract records
Help in Pipelined function to extract records [message #323242] Tue, 27 May 2008 21:39 Go to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi All,
I have created a pipelined function to extract values in a column as sepearate rows but it is giving Error :

My Requirement :
column X in table A have values '1,2,3,4,5'.
Now i want to get these values in 5 rows as single records
so that there are 5 rows.

I created a Pipelined function but before that i created a Object type to get the records.

CREATE Or replace TYPE rawat_test_tab_privs is object
(String_value varchar2 (10) ) ;

Then i created a Type for this object:

CREATE or replace TYPE rawat_test_privs IS TABLE OF rawat_test_tab_privs ;

Now the pipelined function :

CREATE Or replace FUNCTION itp_grants
RETURN rawat_test_privs PIPELINED IS
out_rec rawat_test_tab_privs ;
cur0 sys_refcursor ;
BEGIN
OPEN cur0 FOR
'SELECT * from rawat_test123 ' ;
LOOP
FETCH cur0 INTO out_rec ;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
CLOSE cur0;

RETURN;
END itp_grants ;
/

Note that rawat_test123 has only one column and only one row with values as '1,2,3,4,5'. The result i want as 5 rows.

When i run the Query :

select * from table(itp_grants)

It Throws Error :

ORA-00932: inconsistent datatypes: expected UDT got CHAR.

tried with diff object type but could not get what is the error.

Please comment.


Re: Help in Pipelined function to extract records [message #323245 is a reply to message #323242] Tue, 27 May 2008 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Please comment.
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Help in Pipelined function to extract records [message #323247 is a reply to message #323242] Tue, 27 May 2008 22:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Search http://asktom.oracle.com for his str2tbl or in_list functions, for example:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061
Re: Help in Pipelined function to extract records [message #323251 is a reply to message #323242] Tue, 27 May 2008 22:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQK*Plus and copy and paste your whole session (starting from table description).
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: Help in Pipelined function to extract records [message #323258 is a reply to message #323251] Tue, 27 May 2008 23:33 Go to previous message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi Barbara,
The URL Which you sent did the trick :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Thanks everybody to response.
Previous Topic: OUTERJOIN
Next Topic: natural join
Goto Forum:
  


Current Time: Mon Dec 05 11:20:49 CST 2016

Total time taken to generate the page: 0.09908 seconds