How to import text data to Oracle

Introduction: Oracle DatabaseThe functionality is very good. It is precisely because of the advantages of Oracle databases that Oracle databases have won the favor of many users. In the Dos environment, use the SQL * Loader command to load the Data Loading Function in Oracle Enterprise Manager, a data transfer tool for other databases. Next, we will teach you how to import text data into Oracle databases.

Specific technical implementation

Load in Dos environment

1. First, the server listening service must be enabled.

Test method: Input in Dos

C: \> sqlplus username/password @ serviceName

2. Use the sqlldr command of Oracle to import data.

Prerequisites

1) the Oracle database must have the structure of the data table to be imported.

2) In the following example, a text file named model.txt is a tab-separated text file, which is exported from the Excel table.

3) manually edit a control file for XXX. CTL

4) load data through command lines

Example:

The following files are put under C: \ by default. If not, you need to specify the full path.

1. Command Control file input. ctl content

Keyword used to insert data in the control file

Insert, which is the default mode. The table is required to be empty when data loading starts.

Append: append a new record to the table

Replace the old record and replace it with the newly loaded record.

Truncate, same as above

Use the SQL * Loader command in the Dos window to import data.

C: \> sqlldr userid = system/manager @ serviceName control = input. ctl

The default log file name is input. log.

The default bad record file is input. bad.

Use other database transfer tools

The following uses the SQL Server Import and Export wizard as an example.

1. In the data import/export wizard, set the data source Server. In the instance, select SQL Server as the data source.

2. Specify the Oracle Data source to be imported.

3. You need to configure Oracle attributes.

Note that the user information for logging on to the database is the solution name after the data is imported, that is, the table in the imported SQL Server is marked as username in Oracle. you can follow the prompts below to completely import the data tables and views in SQL Server, or return the columns or rows you want to select using the query statement.

Data Loading in Oracle Enterprise Manager

Log on to the Oracle console. For separate data tables, you can use the data loading tool to specify control files among them, which are consistent with Dos loading and are no longer repeated.

I will introduce so much about importing text data into Oracle databases. If you like this, I will continue to introduce more and better knowledge to you, the purpose is to help everyone.