Home » SQL & PL/SQL » SQL & PL/SQL » Tables function doubt (oracle ,10.2,windows95)
Tables function doubt [message #576058] Thu, 31 January 2013 04:06 Go to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Hi,
In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query
SELECT  * from  TABLE(SPLIT('bbb003,bb004'));

out put is 
bbb003
bb004


now i have to apply same function on column,column is storing data with ',' separated.
and i have tried like but it's throwing missing expression.please advice how i can usethis function on entire column from this table.
SELECT  * from  TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));

Re: Tables function doubt [message #576059 is a reply to message #576058] Thu, 31 January 2013 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what you want to get with this call but what is sure is that you have to create a new function.
Have a look at T.Kyte's print_table function.

Regards
Michel
Re: Tables function doubt [message #576061 is a reply to message #576059] Thu, 31 January 2013 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remind: If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: Tables function doubt [message #576062 is a reply to message #576058] Thu, 31 January 2013 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can try something like (but I cannot test as I have neither your table nor your function):
SELECT  * 
from (select candidates FROM ibis.cw_uploads_inprogress) c, 
     TABLE(SPLIT(c.candidates));

Regards
Michel
Re: Tables function doubt [message #576067 is a reply to message #576062] Thu, 31 January 2013 04:27 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Thanks michel .Like this only i want. I will check
Re: Tables function doubt [message #576068 is a reply to message #576067] Thu, 31 January 2013 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure it works, as I said I have nothing to test with.

Regards
Michel
Re: Tables function doubt [message #576218 is a reply to message #576068] Fri, 01 February 2013 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that OP has given the code of its function in the topic http://www.orafaq.com/forum/m/576176/102589/#msg_576176, we can verify the solution:
SQL> with 
  2    cw_uploads_inprogress as (
  3      select '1,2,3' candidates from dual
  4      union all
  5      select 'a,b,c' from dual
  6    )
  7  SELECT  * 
  8  from (select candidates FROM cw_uploads_inprogress) c, 
  9       TABLE(SPLIT(c.candidates));
CANDI COLUMN_VAL
----- ----------
1,2,3 1
1,2,3 2
1,2,3 3
a,b,c a
a,b,c b
a,b,c c

Regards
Michel
Re: Tables function doubt [message #576239 is a reply to message #576218] Fri, 01 February 2013 12:14 Go to previous message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@krajasekhar.v, do you really use MS Windows 95 and run Oracle 10g on it?
Previous Topic: Date time format error : ORA-01830
Next Topic: Query Help
Goto Forum:
  


Current Time: Sun Sep 21 08:06:44 CDT 2014

Total time taken to generate the page: 0.69304 seconds