Re: Reading column into a string variable

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news: 455954da$0$7731$426a34cc_at_news.free.fr...
|
| "Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 1163452050.065616.276080_at_f16g2000cwb.googlegroups.com...
|| zomer wrote:
|| > Hi,
|| > I need to read all entries of a column into a varchar variable. There
|| > are about 100 entries.... how can i do it in PL/SQL?
|| >
|| > Table
|| > Name
|| > alex
|| > brad
|| > cynthia
|| > .
|| > .
|| > .
|| > .
|| > zomer
|| >
|| > Anticipated output
|| > alex,brad,cynthia.....,zomer
|| >
|| > Thanks.
||
|| It should be fairly easy in PL/SQL to loop through the results of the
|| query and append the value to a variable. You can also do this
|| automatically in SQL, and then just use the result in your PL/SQL code.
||
|| The setup:
|| CREATE TABLE T1 (NAME VARCHAR2(20));
|| INSERT INTO T1 VALUES('alex');
|| INSERT INTO T1 VALUES('brad');
|| INSERT INTO T1 VALUES('cynthia');
|| INSERT INTO T1 VALUES('zomer');
||
|| The SELECT statement:
|| SELECT
|| MAX(SUBSTR(SYS_CONNECT_BY_PATH(NAME,','),2)) NAME_LIST
|| FROM
|| (SELECT
|| NAME,
|| ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
|| FROM
|| T1)
|| CONNECT BY PRIOR
|| ROW_POSITION=ROW_POSITION-1
|| START WITH
|| ROW_POSITION=1;
||
|| The output:
|| NAME_LIST
|| =========
|| alex,brad,cynthia,zomer
||
|| How do this work? The inline view retreives each name, and a number
|| that indicates the sorted order of the name:
|| SELECT
|| NAME,
|| ROW_NUMBER() OVER (ORDER BY NAME) ROW_POSITION
|| FROM
|| T1
||
|| If we then use a CONNECT BY PRIOR, starting with ROW_POSITION 1, we
|| receive as output:
|| NAME_LIST
|| ====================
|| alex
|| brad
|| cynthia
|| zomer
||
|| By specifying SYS_CONNECT_BY_PATH, and eliminating the prefixed , we
|| obtain:
|| NAME_LIST
|| ====================
|| alex
|| alex,brad
|| alex,brad,cynthia
|| alex,brad,cynthia,zomer
||
|| If we then find the maximum value, we are left with the last item:
|| NAME_LIST
|| =========
|| alex,brad,cynthia,zomer
||
|| Charles Hooper
|| PC Support Specialist
|| K&M Machine-Fabricating, Inc.
||
|
| A slighty better query is:
|
| SQL> with
| 2 data as (
| 3 select name,
| 4 row_number() over (order by name) rn,
| 5 count(*) over () cnt
| 6 from t1
| 7 )
| 8 select substr(sys_connect_by_path(name,','),2) name_list
| 9 from data
| 10 where rn = cnt
| 11 connect by prior rn = rn-1
| 12 start with rn = 1
| 13 /
| NAME_LIST
| --------------------------------------------------------------
| alex,brad,cynthia,zomer
|
| 1 row selected.
|
| It avoids the grouping action by precalculating which row will contain the final result.
|
| Regards
| Michel Cadot
|

Forgot to mention: of course the result is limited to 4000 characters. If you expect a larger result you have to use a function such as Tom Kyte's stragg one (see http://asktom.oracle.com)

Regards
Michel Cadot Received on Mon Nov 13 2006 - 23:35:24 CST