Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SOLVED: Re: Getting all rows of sqlplus output on one line
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
> 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
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 10 2006 - 14:15:05 CDT
![]() |
![]() |