Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sql Query

Re: Sql Query

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 19 May 2006 17:02:41 -0700
Message-ID: <bf46380605191702h7d97a579o891b95f38596e97a@mail.gmail.com>


That's a clever bit of SQL.

It doesn't work in 9i however.

On 5/18/06, Igor Neyman <ineyman_at_perceptron.com> wrote:
>
> And one more (totaly different) solution:
>
> CREATE or replace TYPE file_list AS TABLE OF varchar2(513);
> /
> SELECT CAST(COLLECT(file_name) AS file_list)
> FROM dba_data_files
> /
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Igor Neyman
> *Sent:* Thursday, May 18, 2006 1:59 PM
> *To:* smishra_97_at_yahoo.com; oracle-l_at_freelists.org
> *Subject:* RE: Sql Query
>
> Or, without "order by":
>
> with T AS (
> SELECT SYS_CONNECT_BY_PATH(file_name, ',') text, CONNECT_BY_ISLEAF
> FROM ( select 1 verse, rownum piece, file_name from dba_data_files )
> WHERE CONNECT_BY_ISLEAF = 1
> CONNECT BY verse = PRIOR verse
> AND piece - 1 = PRIOR piece
> START WITH piece = 1
> )
> select text from T
> /
> Igor
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Igor Neyman
> *Sent:* Thursday, May 18, 2006 11:44 AM
> *To:* smishra_97_at_yahoo.com; kennaim_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* RE: Sql Query
>
> Wouldn't this be sufficient:
>
> with T AS (
> 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
> order by lvl desc )
> select text from T
> where rownum = 1
> /
>
>
> which is just "internal" part of your query (slightly modified) ?
>
> Igor
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sanjay Mishra
> *Sent:* Thursday, May 18, 2006 10:56 AM
> *To:* kennaim_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* RE: Sql Query
>
> 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_at_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_at_gmail.com>* wrote:
>
> Search for stragg on http://asktom.oracle.com
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sanjay Mishra
> *Sent:* Thursday, May 04, 2006 1:12 PM
> *To:* oracle-l_at_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.
>
> Regds
> Sanjay
> ------------------------------
> Yahoo! Mail goes everywhere you do. Get it on your phone<http://us.rd.yahoo.com/evt=31132/*http:/mobile.yahoo.com/services?promote=mail>
> .
>
>
> ------------------------------
> How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call
> rates.<http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>
>
>
> ------------------------------
> Yahoo! Messenger with Voice.<http://us.rd.yahoo.com/mail_us/taglines/postman3/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com>PC-to-Phone calls for ridiculously low rates.
>
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 19 2006 - 19:02:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US