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@yahoo.com> 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@gmail.com> wrote:
Search for stragg on http://asktom.oracle.com
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Sanjay Mishra Sent: Thursday, May 04, 2006 1:12 PM To: oracle-l@freelists.org 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.
