Summary of Oracle spool usage

In production, you often encounter the need to import a large number of table values to a local text file. there are many methods. Commonly used is the spool command: to output data files that meet the required format, you only need to use character connections during select to standardize the format. For example, see the following table.

SQL>; select ID, username, password from myuser; // test table

1. John 1234

2 Jack 12345

3 rose 2345

4 Joe 384657

5 Tom 384655

6. Jordan 384455

The output must conform to 1, John, 1234. Select ID | ',' | username | ',' | password | ', 'from myuser.

SQL>; select ID | ',' | username | ',' | password | ',' from myuser;

1, John, 1234,

2. Jack, 12345,

Write the following script to output data in the correct format to the file, without any additional unnecessary data.

-- The script file name is expmyusr. SQL, and the file name for storing data is E:/exp.txt

Set echo on -- whether to display the executed command content set feedback off -- whether to display * Rows selected set heading off -- whether to display the field name set verify off -- whether to display the replacement variable before and after replacement statement. Filset trimspool off -- remove space from the field

Set pagesize 1000 -- page size set linesize 50 // set linesize as much as possible based on your needs. The size of the generated file is also large.

Define fil = 'e:/exp.txt'

Prompt *** spooling to & fil

Spool & fil

Select ID | ',' | username | ',' | '"' | password | '"' from myuser;

Spool off;

-- Execution Process

SQL>; @ E:/expmyusr. SQL

* ** Spooling to E:/exp.txt

1, John, "1234"

2, Jack, "12345"

3. Rose, "2345"

4, Joe, "384657"

5, Tom, "384655"

6. Jordan, "384455"

Check that the results meet the requirements.

· Comparison of Two Oracle spool Methods

Generally, we use the spool method to export the tables in the database as text files in two ways, as shown below:

 

 

Method 1: script in the following format

Set colsep ''------ set column Separator

Set trimspool on

Set linesize 120

Set pagesize 2000

Set Newpage 1

Set heading off

Set term off

Spool path + file name

Select * From tablename;

Spool off

 

 

 

Method 2: Use the following script

 

Set trimspool on

Set linesize 120

Set pagesize 2000

Set Newpage 1

Set heading off

Set term off

Spool path + file name

Select col1 | ',' | col2 | ',' | col3 | ',' | col4 | '...' from tablename;

Spool off

Compare the above methods, that is, method 1 uses the set separator and sqlplus uses the set separator to separate the fields. method 2 Concatenates the separator into the SELECT statement, that is, manually controlling the output format.

In practice, I found that the data exported through method 1 has great uncertainty. The possibility of errors when the data imported by this method is then imported by SQL LDR is more than 95%, this is especially true for a large number of data tables, such as tables with 1 million records, and the exported data files are incredibly large.

The format of the data file exported in method 2 is quite regular, and the size of the data file may be about 1/4 of that in method 1. When the data files exported by this method are imported by sqlldr, there is little possibility of errors, and the data files can be imported successfully.

Therefore, in practice, we recommend that you use method 2 to manually control the spool file format, which can reduce the possibility of errors and avoid many detours.