合 How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ? (Doc ID 1967617.1)
👉 本文共约4725个字，系统预计阅读时间或需18分钟。
目录
How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ? (Doc ID 1967617.1)
GOAL
How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ?
Below is the sample PL/SQL function to export the table data into a CSV File.
This solution takes data from a table with CLOB column and writes it to a csv file using the UTL_FILE built-in package.
Note that UTL_FILE has a limit of 32K, so that combined length of all the columns in a row should be less than or equal to 32k.
SOLUTION
1)Login to the DB user
1 | sqlplus scott/tiger |
2)Create a sample table
1 | CREATE TABLE TESTCLOB(ID NUMBER, MYCLOB1 CLOB, MYCLOB2 CLOB ); |
3)Insert the sample data into to the above table TESTCLOB
1 2 3 4 5 6 | 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; |
4)Check the sample data.
1 2 3 4 5 6 7 8 9 10 11 | column MYCLOB1 format a20 column MYCLOB2 format a20 SELECT * FROM TESTCLOB; ID MYCLOB1 MYCLOB2 --------- -------------------- -------------------- 1 Sample row 11 Sample row 12 2 Sample row 21 Sample row 22 3 Sample row 31 Sample row 32 4 Sample row 41 Sample row 42 5 Sample row 51 Sample row 52 |
5)Create a PL/SQL directory object pointing to a Valid physical directory in the database server where csv file is created and make sure that this directory has proper write permission to Oracle user.
1 | CREATE OR REPLACE DIRECTORY MYDIR AS '/testcases/csv'; |
6)Create the below function to export the table data into a CSV File.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | 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 is l_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; 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, 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; 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; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
