RE: SOLVED: Re: Getting all rows of sqlplus output on one line

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 10 Oct 2006 12:15:05 -0700 (PDT)
Message-ID: <20061010191506.38403.qmail@web52806.mail.yahoo.com>

I used your statement literally and tried it out (didn't realize it need a change in the function too)

quote> I used a replace around the stragg function but was wondering if it would be possible (ex: stragg(col,';'))

BTW, I use another method using 10g's COLLECT feature:

CREATE OR REPLACE
TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); /

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab,
 p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;     END IF;
    l_string := l_string || p_varchar2_tab(i);   END LOOP;
  RETURN l_string;
END tab_to_string;
/

select tab_to_string(
 CAST(COLLECT( username ) AS t_varchar2_tab),';' )

    AS username
from dba_users
where username like 'SYS%';

USERNAME

---

SYSTEM;SYS;SYSMAN

• Marco Gralike <Marco.Gralike_at_AMIS.nl> wrote:

> I have NO idea what you wrote about, but google gave
> me the answer within two clicks and typing one word
> "stragg". Look simple to me...
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402
>
> ________________________________
>
> From: oracle-l-bounce_at_freelists.org on behalf of
> Deepak Sharma
> Sent: Tue 10-10-2006 18:52
> To: rjamya_at_gmail.com; kennaim_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: SOLVED: Re: Getting all rows of sqlplus
> output on one line
>
>
>
> How?
>
> select stragg(username,';') users
> from dba_users
> where username like 'SYS%'
> order by username
> /
> select stragg(username,';') users
> *
> ERROR at line 1:
> ORA-06553: PLS-306: wrong number or types of
> arguments
> in call to 'STRAGG'
>
>
> --- rjamya <rjamya_at_gmail.com> wrote:
>
> > yes, try it, it is very simple actually. I had
> done
> > it some time ago.
> >
> > Raj
> >
> > On 10/9/06, Ken Naim <kennaim_at_gmail.com> wrote:
> > > Is there a way to modify the stragg and
> > ODCIAggregateIterate function to
> > > accept a variable for the separator. I used a
> > replace around the stragg
> > > function but was wondering if it would be
> possible
> > (ex: stragg(col,';'))
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com <http://mail.yahoo.com/>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 10 2006 - 14:15:05 CDT