# Oracle SQL\$1Loader and MySQL mysqlimport and LOAD DATA
<a name="chap-oracle-aurora-mysql.hadr.dump"></a>

With AWS DMS, you can efficiently migrate data from flat files into AWS databases using Oracle SQL\$1Loader, MySQL mysqlimport, and `LOAD DATA` commands. These utilities facilitate bulk data loading from external files into database tables.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[No feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/pb-compatibility-0.png)   |  N/A  |  N/A  |  The tool isn’t compatible.  | 

## Oracle usage
<a name="chap-oracle-aurora-mysql.hadr.dump.oracle"></a>

SQL\$1Loader is a powerful utility that imports data from external files into database tables. It has strong parsing engine with few limitations on data formats.

You can use SQL\$1Loader with or without a control file. A control file enables handling more complicated load environments. For simpler loads, use SQL\$1Loader without a control file. The same also refers to SQL\$1Loader Express.

The outputs of SQL\$1Loader include the imported database data, a log file, a bad file or rejected records, and a discard file, if this option is turned on.

### Examples
<a name="chap-oracle-aurora-mysql.hadr.dump.oracle.examples"></a>

Oracle SQL\$1Loader is well suited for large databases with a limited number of objects. The process of exporting from a source database and loading to a target database is very specific to the schema. The following example creates sample schema objects, exports from a source, and loads into a target database.

Create a source table.

```
CREATE TABLE customer_0 TABLESPACE users
  AS SELECT rownum id, o.* FROM all_objects o, all_objects x
    where rownum <= 1000000;
```

On the target Amazon RDS instance, create a destination table for the loaded data.

```
CREATE TABLE customer_1 TABLESPACE users
  AS select 0 as id, owner, object_name, created
    from all_objects where 1=2;
```

The data is exported from the source database to a flat file with delimiters. This example uses SQL\$1Plus. For your data, you will likely need to generate a script that does the export for all the objects in the database.

```
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
set linesize 800
HEADING OFF FEEDBACK OFF array 5000 pagesize 0
spool customer_0.out
SET MARKUP HTML PREFORMAT ON SET COLSEP ',' SELECT id,
  owner, object_name, created FROM customer_0;
spool off
```

Create a control file describing data. Depending on data, you may need to build a script that provides this functionality.

```
cat << EOF > sqlldr_1.ctl
LOAD DATA
INFILE customer_0.out
into table customer_1
APPEND
fields terminated by "," optionally enclosed by '"'
(id POSITION(01:10) INTEGER EXTERNAL,
owner POSITION(12:41) CHAR,
object_name POSITION(43:72) CHAR,
created POSITION(74:92) date "YYYY/MM/DD HH24:MI:SS")
```

Import data using SQL\$1Loader. Use the appropriate user name and password for the target database.

```
sqlldr cust_dba@targetdb control=sqlldr_1.ctl BINDSIZE=10485760 READSIZE=10485760 ROWSS=1000
```

For more information, see [SQL\$1Loader](https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader.html#GUID-8D037494-07FA-4226-B507-E1B2ED10C144) in the *Oracle documentation*.

## MySQL usage
<a name="chap-oracle-aurora-mysql.hadr.dump.mysql"></a>

You can use the two following options as a replacement for the Oracle SQL\$1Loader utility:
+  **MySQL Import** using an export file similar to a control file.
+  **Load from Amazon S3 File** using a table-formatted file on Amazon S3 and loading it into a MySQL database.

MySQL Import is a good option when you can use a tool from another server or a client. The `LOAD DATA` command can be combined with metadata tables and EVENT objects to schedule loads.

For more information, see [Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html) in the *User Guide for Aurora* and [mysqlimport — A Data Import Program](https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html) in the *MySQL documentation*.