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.
How to Convert the Data from BLOB to CLOB using PL/SQL (Doc ID 235142.1)
How To Extract BLOB Data Into Individual Files (Doc ID 1602415.1)
DBMS_LOB Loading and Extracting Binary File To Oracle Database (Doc ID 1307346.1)
How to Write BLOBs Stored Inside the Database Out to Files (Doc ID 330146.1)
How to Write CLOB Data > 32K Out to a File? (Doc ID 358641.1)
How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ? (Doc ID 1967617.1)
How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS (Doc ID 471715.1)
Please refer to the following documents.
This document also describes how to dump data from Oracle in CSV format and import it to MySQL using the LOAD DATA command.
Migration from Oracle to MySQL (Doc ID 1477151.1)
REFERENCES
NOTE:1307346.1 - DBMS_LOB Loading and Extracting Binary File To Oracle Database
NOTE:330146.1 - How to Write BLOBs Stored Inside the Database Out to Files
NOTE:358641.1 - How to Write CLOB Data > 32K Out to a File?
NOTE:1967617.1 - How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ?
NOTE:471715.1 - How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS
NOTE:235142.1 - How to Convert the Data from BLOB to CLOB using PL/SQL
NOTE:119897.1 - ORA-22992 When Trying To Select Lob Columns Over A Database Link
NOTE:1477151.1 - Migration from Oracle to MySQL
NOTE:1602415.1 - How To Extract BLOB Data Into Individual Files
NOTE:2123615.1 - Migrate Oracle Table Containing BLOB/CLOB To MySQL
Migration from Oracle to MySQL (Doc ID 1477151.1)
GOAL
How can I migrate my database from Oracle to MySQL?
SOLUTION
The task of migrating a complete schema from one database to another is not always simple. Apart from the physical aspect from getting the data from one machine to another, or even a different geographical location, some of the more complex tasks are converting between different data types, stored procedures and simple incompatible differences that may occur between the two databases.
MySQL Migration Toolkit was the package of choice originally, but this has been superseded by MySQL Workbench. At this point however, the migration capabilities of MySQL Workbench are still being developed into a complete migration solution for even the major commercial databases.
Exporting Data from Oracle
Exporting the existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual input and can be slower than using a migration tool. One of the most popular tools for exporting data from Oracle is Oracle SQL Developer. The Oracle SQL Developer tool is capable of exporting data from Oracle tables in numerous formats, like Excel, SQL insert statements, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, etc.
You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that could either be imported via LOAD DATA INFILE or by creating a CSV table for immediate access.
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
1 |
You can also use the "set colsep" command if you don't want to put the commas in by hand. This saves a lot of typing. Example:
set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1 where col2 = 'XYZ';
spool off
1 |
Using PL/SQL
PL/SQL's UTL_FILE package can also be used to unload data. Example:
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Migrating LOB's from Oracle
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, write to CSV with the converted value in hexadecimal.
When loading on 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);
Following links will be useful.
How to Convert the Data from BLOB to CLOB using PL/SQL (Doc ID 235142.1)
How To Extract BLOB Data Into Individual Files (Doc ID 1602415.1)
DBMS_LOB Loading and Extracting Binary File To Oracle Database (Doc ID 1307346.1)
How to Write BLOBs Stored Inside the Database Out to Files (Doc ID 330146.1)
How to Write CLOB Data > 32K Out to a File? (Doc ID 358641.1)
How to Export The Table with a CLOB Column Into a CSV File using UTL_FILE ? (Doc ID 1967617.1)
How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS (Doc ID 471715.1)
Migrate Oracle Table Containing BLOB/CLOB To MySQL (Doc ID 2123615.1)
Importing Data into MySQL
Once the data has been exported from Oracle into a flat file, then it can be loaded into MySQL. This step continues with the expectation that an identical schema structure was created in the MySQL database to accept the data.
The most common method to read a flat file into MySQL is using the LOAD DATA INFILE command. This is of the form:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;``
This will read the exported data from the 'data.txt' file into the table 'tbl_name' using the specified separators.
There is also the LOAD XML INFILE if the exported data is in XML format.
Another method that may be even quicker for load is to use the CSV storage engine capabilities. This requires the table to again be created with the appropriate schema and the storage engine specified as CSV. For example:
CREATE TABLE mytable (
name varchar(50) NOT NULL,
address varchar(100) NOT NULL,
zipcode CHAR(5) NOT NULL
) ENGINE=CSV;
Once the table is created, a .csv file will appear in the data directory corresponding to the table ready to store data. Copy the exported CSV file over the existing 'mytable.csv' data file and execute a FLUSH TABLES to update the table information. Now you have access to the data and can use a INSERT INTO new_table ... SELECT to read data quickly from the CSV table into a more appropriate table for your production system.
Migration Tools
Depending on the setup you use, the following may be interest in your migration tasks:
Note 1023411.1: How Can I Connect Oracle to MySQL? Heterogeneous Services and GoldenGate For Live Access, SQL Developer For Migrations
Migration of Stored Procedures, Functions, Packages, Triggers, Views, Sequences must be performed using third party tools and needs manual effort. This document highlights method to perform data migration.
There are a host of third party tools, some of which are open source. For example:
- http://www.sqlines.com/oracle-to-mysql
- http://kettle.pentaho.com/
- http://www.convert-in.com/ora2sql.htm
- http://www.ispirer.com/products/oracle-to-mysql-migration
REFERENCES
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
https://dev.mysql.com/doc/refman/5.5/en/load-data.html
http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm
NOTE:210261.1 - Oracle Migration Workbench 9.2.0.1 FAQ MYSQL
NOTE:1023411.1 - How Can I Connect Oracle to MySQL? Heterogeneous Services and GoldenGate For Live Access, SQL Developer For Migrations
http://docs.oracle.com/cd/E12151_01/nav/portal_booklist.htm
NOTE:1517543.1 - MySQL Workbench: Generic Migration from Oracle DB Fails
NOTE:2123615.1 - Migrate Oracle Table Containing BLOB/CLOB To MySQL
https://dev.mysql.com/doc/refman/5.5/en/load-xml.html
