当前位置：首页 > news >正文

Oracle导出clob字段到csv

news 2025/6/26 23:26:25

使用UTL_FILE

ref: How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ?(Doc ID 1967617.1)

--preapre data
CREATE TABLE TESTCLOB(ID NUMBER, MYCLOB1 CLOB, MYCLOB2 CLOB );
INSERT INTO TESTCLOB(ID,MYCLOB1,MYCLOB2) VALUES(1,'Sample row 11', 'Sample row 12');
INSERT INTO TESTCLOB(ID,MYCLOB1,MYCLOB2) VALUES(2,'Sample row 21', 'Sample row 22');
INSERT INTO TESTCLOB(ID,MYCLOB1,MYCLOB2) VALUES(3,'Sample row 31', 'Sample row 32');
INSERT INTO TESTCLOB(ID,MYCLOB1,MYCLOB2) VALUES(4,'Sample row 41', 'Sample row 42');
INSERT INTO TESTCLOB(ID,MYCLOB1,MYCLOB2) VALUES(5,'Sample row 51', 'Sample row 52');
COMMIT;column MYCLOB1 format a20
column MYCLOB2 format a20
SELECT * FROM TESTCLOB;--create directory
CREATE OR REPLACE DIRECTORY MYDIR AS '/u02/';--create function
CREATE OR REPLACE FUNCTION EXPORT_TABLE_TO_CSV_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 isl_output        utl_file.file_type;l_theCursor     integer default dbms_sql.open_cursor;l_columnValue   varchar2(2000);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;
BEGINdbms_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 loopif l_col_desc(i).col_type = 112 thendbms_sql.define_column(l_theCursor, i, l_columnValClob);elsedbms_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 thenl_output := utl_file.fopen(p_dir, p_filename, 'w', 32767);if p_is_head thenfor i in 1 .. l_col_desc.count looputl_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;loopl_separator := '';for i in 1 .. l_colCnt loopif l_col_desc(i).col_type = 112 thenl_offset := 1;dbms_sql.column_value(l_theCursor, i, l_columnValClob);utl_file.put(l_output, l_separator);loopl_columnValue := dbms_lob.substr(l_columnValClob,2000,l_offset);-- dbms_output.put_line(l_columnValue);l_offset := l_offset + 2000;utl_file.put(l_output, l_columnValue);exit when trim(l_columnValue) is null;end loop;elsedbms_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
DECLAREl_sql dbms_sql.varchar2a;l_cnt integer;
BEGINl_sql(1) := 'SELECT * FROM TESTCLOB';l_cnt := export_table_to_csv_file(l_sql,',','MYDIR','csvsample.csv',TRUE);
END;
/