Re: Reading column into a string variable

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 13 Nov 2006 13:07:30 -0800
Message-ID: <1163452050.065616.276080@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. Received on Mon Nov 13 2006 - 15:07:30 CST