Hi guys,
I hope someone might be able to assist me here :)
I'm trying to query data from table with a CLOB column into excel file.
The CLOB column is split over several lines messing the output excel file.
I need the CLOB column output in a single line alongside the rest of the data in the row.
Could anyone tell me what I might be doing wrong?
I am using the following function and unnamed block to query the data.
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION EXPORT_TABLE_TO_XLSX_FILE
(
p_query in dbms_sql.varchar2a,
p_separator in varchar2 default ',',
p_dir in varchar2,
p_filename in varchar2,
p_is_head in boolean default false
)
RETURN NUMBER
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_columnValClob clob;
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_col_desc dbms_sql.desc_tab;
l_offset integer;
BEGIN
dbms_sql.parse
(
l_theCursor
, p_query
, p_query.first
, p_query.last
, true
, dbms_sql.native
);
dbms\_sql.describe\_columns
(
l\_theCursor
, l\_colCnt
, l\_col\_desc
);
for i in 1 .. l\_colCnt loop
if l\_col\_desc(i).col\_type = 112 then
dbms\_sql.define\_column
(
l\_theCursor
, i
, l\_columnValClob
);
else
dbms\_sql.define\_column
(
l\_theCursor
, i
, l\_columnValue
, 4000
);
end if;
end loop;
l\_status := dbms\_sql.execute(l\_theCursor);
if dbms\_sql.fetch\_rows(l\_theCursor) > 0 then
l\_output := utl\_file.fopen( p\_dir, p\_filename, 'w', 32767 );
if p\_is\_head then
for i in 1..l\_col\_desc.count
loop
utl\_file.put(l\_output, l\_separator || l\_col\_desc(i).col\_name);
l\_separator := p\_separator;
end loop;
utl\_file.new\_line( l\_output );
end if;
loop
l\_separator := '';
for i in 1 .. l\_colCnt
loop
if l\_col\_desc(i).col\_type = 112
then
l\_offset := 1;
dbms\_sql.column\_value
(
l\_theCursor
, i
, l\_columnValClob
);
utl\_file.put( l\_output, l\_separator );
loop
l\_columnValue := dbms\_lob.substr(l\_columnValClob, 20000, l\_offset);
-- dbms\_output.put\_line(l\_columnValue);
l\_offset := l\_offset + 20000;
utl\_file.put( l\_output, l\_columnValue);
exit when trim(l\_columnValue) is null;
end loop;
else
dbms\_sql.column\_value
(
l\_theCursor
, i
, l\_columnValue
);
utl\_file.put( l\_output, l\_separator || l\_columnValue );
end if;
l\_separator := p\_separator;
end loop;
utl\_file.new\_line( l\_output );
l\_cnt := l\_cnt+1;
exit when ( dbms\_sql.fetch\_rows(l\_theCursor) \<= 0 );
end loop;
end if;
dbms\_sql.close\_cursor(l\_theCursor);
utl\_file.fclose( l\_output );
return l\_cnt;
END;
/
set serverout on
DECLARE
l_sql dbms_sql.varchar2a;
l_cnt integer;
BEGIN
l_sql(1) := 'SELECT * FROM TRENTADM.TWF_STEP_EMAIL where rownum <2';
l_cnt := EXPORT_TABLE_TO_XLSX_FILE
(
l_sql,
',',
'MYDIR',
'TWF_STEP_EMAIL.csv',
TRUE
);
END;
/