合 How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ? (Doc ID 1967617.1)
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; |
7)Execute the PL/SQL function export_table_to_csv_file as below :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | set serverout on DECLARE l_sql dbms_sql.varchar2a; l_cnt integer; BEGIN l_sql(1) := 'SELECT * FROM TESTCLOB'; l_cnt := export_table_to_csv_file ( l_sql, ',', 'MYDIR', 'csvsample.csv', TRUE ); END; / |
8)Now got to the physical directory and check the file csvsample.csv
1 2 3 4 5 6 7 8 9 10 11 | [xxxx]/testcases/csv> cd /testcases/csv [xxxx]/testcases/csv> ls -l csvsample.csv -rw-r--r-- 1 irefresh refresh 169 Feb 4 02:12 csvsample.csv [xxxx]/testcases/csv> cat csvsample.csv 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 |
How to migrate BLOB/CLOB data from Oracle to MySQL (Doc ID 2479980.1)
GOAL
To provide how to migrate BLOB/CLOB data from Oracle to MySQL
SOLUTION
For BLOB type, it is possible to migrate by converting it to a hexadecimal character string and then loading it.
When dumping BLOB data from Oracle, please write to CSV with the converted value in hexadecimal.
(In general, you can use rawtohex() in a stored procedure to change BLOB to HEX. However, this document is managed by the MySQL support team and we are not covered Oracle DB. If you have any questions in the dump process of the Oracle DB side, please contact Oracle DB support.)
When loading on the MySQL side, use UNHEX function to return from binary to hexadecimal.
The following is an example in the LOAD DATA statement. We set UNHEXed value using SET in longblog type column.
mysql> CREATE TABLE (id int, data longblob);
mysql> LOAD DATA LOCAL INFILE '/path/to/test.mycsv'
INTO TABLE test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\'
LINES TERMINATED BY '\ n'
(id, @ col2) SET data = UNHEX (@ col2);
How to export data from Oracle:
The following link will be useful.
