RE: Sql Query
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: kennaim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 May 2006 07:55:45 -0700 (PDT)
Few Weeks Back I posted a question and got few very good response. Actually
What I am looking is the big list in single SQL query and cannot use the PL sql
or allowed to create the SQL function, I had finally used good points from all
reply and created the following SQL. Here I used file_name from dba_data_files
. I thought it is good to share as somebody who is reall expert in SQL can do
some more fine-tuning. This will atleast work good for mine requirement but may
give some issue if the Text cross the Concat restriction.
select text from (
select verse, lvl, text, rank() over(partition by verse order by lvl desc) rn
from
(
SELECT verse, LEVEL lvl, SYS_CONNECT_BY_PATH(file_name, ',') text
FROM ( select 1 verse, rownum piece, file_name from dba_data_files )
CONNECT BY verse = PRIOR verse
AND piece - 1 = PRIOR piece
START WITH piece = 1
)x
) y
where rn=1
/
Any advice or suggestion are most welcome.
Tx
Sanjay
Sanjay Mishra <smishra_97@xxxxxxxxx> wrote:
Ken
SQL> select stragg(table_name) from user_tables
2 /
select stragg(table_name) from user_tables
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.STRING_AGG_TYPE", line 17
ORA-06512: at line 1
It works for Group by Clauses like
select table_name,stragg(index_name) from user_indexes group by table_name
Regds
Sanjay
Ken Naim <kennaim@xxxxxxxxx> wrote:
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
Search for stragg on http://asktom.oracle.com
---------------------------------
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sanjay Mishra
Sent: Thursday, May 04, 2006 1:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Sql Query
Sql Gurus
I need to create the sql script that can display all tables from
user_tables in one line seperated by ",". Is there any straighforward SQL that
can display the result in Horizontal instead of single table in each row.
Regds
Sanjay
---------------------------------
Yahoo! Mail goes everywhere you do. Get it on your phone.
---------------------------------
How low will we go? Check out Yahoo! Messenger?s low PC-to-Phone call rates.
---------------------------------
Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.
Other related posts:
