Import and Export Oracle Text Files (sqlldr & Spool)

About Export
Principle
The SQL plus report function is used to generate text files, but the generated text files are empty for analysis reasons (only press enter does not have any other). You can use a third-party method to generate standard text.
A learning process
1. Create a table
　　
SQL> Create Table testinout (ID number (10), username varchar2 (10), date1 date, money number (6, 2 ));
　　
2. Add data
SQL> insert into testinout (ID, username, date1, money) values (1, 'will ', to_date ('29-12-2001', 'dd-mm-yyyy '), 33.33)
SQL> insert into testinout (ID, username, date1, money) values (2, 'ddxxk', to_date ('29-12-2002 ', 'dd-mm-yyyy '), 443.33)
SQL> insert into testinout (ID, username, date1, money)
Values (1234567890, 'thisistest ', to_date ('29-12-2002', 'dd-mm-yyyy'), 4443.33)
　　
3. Set the column spacing to '(no space)
SQL> set colsep'
　　
4
SQL> column ID format A10 truncate
SQL> column money format A6 truncate
SQL> select to_char (ID, '0000000009s')
ID, username, to_char (date1, 'yyyymmdd')
Date1, to_char (money * 100, '000000s') as money from testinout;
Id username date1 money
----------------------------------
0000000001 test 20011229003333
0000000002 ddxxkk 20021229055500
1234567890thisistest20021229444333
　　
　　
B
Test process
1. write scripts
The content of testinout. SQL is as follows:
　　
Set colsep'
Set trimspool on
Set linesize 120
Set pagesize 2000
Set Newpage 1
Set heading off
Set term off
Column money format A6 truncate
Column ID format A10 truncate
Spool D:/III
Select to_char (ID, '0000000009s')
ID, username, to_char (date1, 'yyyymmdd')
Date1, to_char (money * 100, '000000s') as money from testinout;
Spool off
Exit;
　　
2. Execute the script
Sqlplus user1/user1 @ my8i @ D:/testinout. SQL
Or
Sqlplus user1/user1 @ D:/testinout. SQL> D:/T
　　
3 D:/III. lst content:
　　
0000000001 test 20011229003333
0000000002 ddxxkk 20021229055500
1234567890thisistest20021229444333
1234567890thisi Chinese Character t20021229444333
　　
4
Complete script I. SQL for III. lst
　　
Create Table testinout (ID number (10), username varchar2 (10), date1 date, money number (6, 2 ));
Insert into testinout (ID, username, date1, money) values (1, 'test', to_date ('29-12-2001 ', 'dd-mm-yyyy'), 33.33 );
Insert into testinout (ID, username, date1, money) values (2, 'ddxxkk ', to_date ('29-12-2002', 'dd-mm-yyyy '), 443.33 );
Insert into testinout (ID, username, date1, money) values (1234567890, 'thisistest ', to_date ('29-12-2002', 'dd-mm-yyyy'), 4443.33 );
Set colsep'
Set trimspool on
Set linesize 120
Set pagesize 2000
Set Newpage 1
Set heading off
Set term off
Column money format A6 truncate
Column ID format A10 truncate
Spool D:/III
Select to_char (ID, '0000000009s')
ID, username, to_char (date1, 'yyyymmdd')
Date1, to_char (money * 100, '000000s') as money from testinout;
Spool off
　　
Run
D:/> sqlplus user1/user1 @ my8i @ I. SQL
　　
C
Related commands:
　　
Set the characters between columns
Set colsep 'characters'
　　
Whether to delete trailing spaces before Spool writes to a file
Set trimspool ON/OFF
　　
Specify the width of a report line (number of characters. One Chinese Character occupies 2 places), linesize option range (from 1 to 32767)
Set linesize row width
　　
Specifies the number of rows on the page. The default value is the range of the pagesize option for 24 rows (from 0 to 50000)
Set pagesize page number
　　
Control Column Title printing; on by default
Set heading on/off
That is to say, whether the following information in step 1 is displayed
Id username date1 money
----------------------------------
　　
The number of blank lines before the new page. If it is equal to 0, a line break will appear before the new page's 1st characters.
Number of set Newpage rows
　　
Input content to file
Spool off/file name
　　
Determines whether SQL * Plus is displayed on the screen. The default value is on and the value is off, which indicates that SQL * Plus is directly executed from the script.
Set term off
　　
　　
About Import
　　
Principle
Use SQL * loader (sqlldr) to import text files to data tables,
Sqlldr uses the control file (control = 'filename ') for corresponding work.
Two formats are available for text files.
Fixed Length record, fixed length of each record)
Test 1234 33.44
Ddxxkk 1211 123.00
Variable Length records, separated by specific delimiters (the following example uses commas as delimiters)
Test, 1234, 33.44
Ddxxkk, 1211,123.00
　　
Skip the empty row System
Test
1. Write the script D:/in. CLT.
　　
Load data
Infile 'd:/III. lst'
Append
Into Table testinout
(ID position (0:10) integer external,
Username position (11: 20) Char,
Date1 position (21:28) date 'yyyymmdd ',
Money position (29:34) zoned (6, 2)
)
　　
2. Execute the script
D:/> sqlldr user1/user1 @ my8i control = 'd:/in. CTL'
　　
3. analysis results
Program self-generated log D:/in. log, you can use the sqlldr parameter to specify log = 'file'
Program generated bad data file D:/III. Bad you can use the sqlldr parameter to specify bad = 'file'