In my project, I need export multiple rows from a single column to single row text using utl file
Example below
My table : Employees
Employee_name
-----------------------------
Smith
John
Tom
Adam
And my output text file should export like below
SmithJohnTomAdam
There should not any space are separator between the rows. Even a million rows should export into
Single row in text format
Well, if you know the final string will be less than 4,000 bytes long* you can use listagg in SQL:
with rws as (
select rownum r, lpad(chr(rownum+64), 3, chr(rownum+64)) str
from dual
connect by level <= 5
)
select listagg(str, '') within group (order by r) s from rws;
S
AAABBBCCCDDDEEE
If it'll be longer than that, but < 32,767 you can build up the string in PL/SQL:
declare
s varchar2(32767);
begin
for rws in (
select rownum r, lpad(chr(rownum+64), 3, chr(rownum+64)) str
from dual
connect by level <= 10000
) loop
s := s || rws.str;
end loop;
dbms_output.put_line ('Length: ' || length(s));
dbms_output.put_line ('Start: ' || substr(s, 1, 30));
end;
/
Length: 30000
Start: AAABBBCCCDDDEEEFFFGGGHHHIIIJJJAnd if you're longer than 32,767 bytes, you can use a clob:
declare
s clob;
begin
dbms_lob.createtemporary ( s , true );
for rws in (
select rownum r, lpad(chr(rownum+64), 3, chr(rownum+64)) str
from dual
connect by level <= 20000
) loop
dbms_lob.append ( s, rws.str );
end loop;
dbms_output.put_line ('Length: ' || dbms_lob.getlength(s));
dbms_output.put_line ('Start: ' || dbms_lob.substr(s, 30, 1));
end;
/
Length: 60000
Start: AAABBBCCCDDDEEEFFFGGGHHHIIIJJJ* If you're on 12.1 and up you can go up to 32,767 with listagg if you have MAX_STRING_SIZE set to EXTENDED.