Comparison between the spool commands in Oracle

To output data files in the correct format, you only need to use character links 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 statement before and after substitution variables are replaced. Fil Set trimspool off -- remove space from the field Set pagesize 1000 -- page size Set linesize 50 // set the 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 Between 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.

1. Comparison between Oracle and SQL Server Transaction Processing
2. Explain several paging query statements of Oracle
3. Explain several Oracle Performance Tuning Methods