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

0    646    1

Tags:

👉 本文共约4747个字,系统预计阅读时间或需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

2)Create a sample table

3)Insert the sample data into to the above table TESTCLOB

4)Check the sample data.

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.

6)Create the below function to export the table data into a CSV File.

7)Execute the PL/SQL function export_table_to_csv_file as below :

8)Now got to the physical directory and check the file csvsample.csv

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.

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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

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

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:

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

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部