Import and Export Oracle Text Files (sqlldr & Spool)

Source: Internet
Author: User
Tags date1

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'

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.