Hi,
This is tested on Oracle Database 11g Release 11.2.0.3.0
Any good solution to solve below.
create table a
(
a_ Number,
b_ VARCHAR2(4000)
);
delete from A;
commit;
-- Loading 4000 Characters to the column b_...
insert into A (a_, b_)
values (1,
'zzzasdkasgdkasjhdkashdkajshdkasjhdkjashdkaaasahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkshdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskajdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasjdhkasjdhkjasdhjkasdhkasjdhkajshdkjahsdkjhasdkjsahdkjahsdkjhsajkdhaskdjhaskjdhjaksdhkasjdhasdkasgdkasjhdkashdkajshdkasjhdkjashdksahdkashdaskdjhsadkjhaskdhkasfa');
-- Loading 3 Characters to the column b_...
insert into A (a_, b_)
values (2, 'abc');
commit;
-- 2 records loaded
-- Done.
-- Trying to use aggregate function
SELECT LISTAGG(b_, ', ') WITHIN GROUP (ORDER BY b_) "B"
FROM A;
My LISTAGG function go beyond 4000 bytes, So is there anyother way to do the same?
Thanks & Cheers,
dark.