Shulou(Shulou.com)06/01 Report--

0. Catalogue

1. Overview

two。 Generate a text file

2.1 query statement generates text file

2.2 spool exports database data to a text file

2.2.1 Grammar

2.2.2 set the sqlplus variable

2.2.3 DIY Export data template

2.3 ociuldr tools generate text files in windows environment

2.3.1 ociuldr export example

2.3.2keyword detailed explanation

3.sqlldr imports text files into the database

3.1 Overview

3.2 cases 1 simple

3.3 case 2 need to convert data type

3.4 case 3 needs to skip some lines

More than 4 txt in 3.5 cases

4. Table export and guide examples

-

Overview

In oracle database, table data can be exported to text files through sql statements, tables can be exported to text files through spool, or table data can be exported to text files through ociuldr tools, and then text files can be exported to tables that have been built through sqlldr. It is often used to synchronize production data to the development environment, or to migrate table data. When exporting a text file, try to keep the date

-

two。 Generate a text file

2.1 query statement generates text file

Scott > select EMPNO | |'| ENAME | |'| JOB | |'| MGR | |'| HIREDATE | |'| SAL |'| COMM | |'| | DEPTNO from emp

-- then paste the query results into a text file.

2.2 spool exports database data to a text file

2.2.1 Grammar

Scott > spool / tmp/emp.txt-- opens spool and indicates the path where the txt file is generated

Scott > select * from emp;-- execute sql

Scott > spool off-- turn off spool

Cd / tmp

Cat emp.txt

SCOTT > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

.

14 rows selected.

SCOTT > spool off

You can see that after the first spool, all query statements and query results between closing spool are displayed in a text file.

2.2.2 set variables for sqlplus

* sometimes if we want to beautify or format the contents of a text file, we need to set the variable of sqlplus

Scott > show all-- queries the variables of the current session sqlplus

You can enter set directly to qualify the sqlplus variable for the current session

For example, the default prompt after sqlplus login is sql >

The sql > set sqlprompt "_ user >" prompt becomes the current login user, such as: scott >

Common and commonly used are:

Set echo off;-- does not display SQL statements that are being executed in the script

Set term off;-- does not display the results on the screen and exports them directly to a file.

Set heading off;-- does not output column names

Set feedback off;-the default number of rows greater than 6 will show xx rows selected.

Set linesize 100th Murray-100 characters per line

Set pagesize 800t Murray-800 lines per page, default 24

Set colsep |;-- sets the delimiter between columns

Set verify off;-used to bind variables without displaying old new information

SCOTT > select * from emp where EMPNO=7369

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

-- bind variables

SCOTT > select * from emp where EMPNO=&EMPNO

Enter value for EMPNO: 7369

Old 1: select * from emp where EMPNO=&EMPNO

New 1: select * from emp where EMPNO=7369

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

2.2.3 Export data template

Cd / tmp

Vi spool.sql

-- 20161222 for spool generates a text file

Set echo off

Set heading off

Set feedback off

Set pagesize 0

Set colsep

Set verify off

Spool & 1

Select * from & 2

Spool off

Scott > @ / tmp/spool.sql / tmp/emp.txt emp-- is exported to a txt file

Scott > @ / tmp/spool.sql / tmp/emp.csv emp-- is exported to a CSV file

2.3 ociuldr tool generates text files in windows environment (ociuldr.exe needs to be downloaded by yourself)

2.3.1 ociuldr export example

Ociuldr is not an internal tool. Ociuldr.exe,oracle client configuration is required in the folder where bat resides.

Example 1:

Step 1: download ociuldr.exe

Step 2: create a new file, rename it to out.bat, and enter in out.bat:

Ociuldr user=scott/tiger@orcl query= "select * from emp" field=# head=YES batch=2 file= "test_%%d.txt"

(you can enter it directly on the command line)

Step 3: double-click out.bat

Note: the bat file and ociuldr must be in the same folder

Example 2:

Step 1: download ociuldr.exe

Step 2: create a new file, rename it to out.bat, and enter in out.bat:

Oociuldr.exe userscott/tiger@orcl sql=.\ emp.sql field= "|"

File=.\ emp.txt log=.\ emp.log

Step 3: generate an emp.sql file with query statements without the final semicolon

Step 4: double-click out.bat

Note: bat file and ociuldr must be in the same folder, sql file is not needed, only ensure that the path is correct

2.3.2keyword detailed explanation

User: specify the user name, password, and tnsname of the target database

Sql: sets the sql file to be executed

Query: sets the sql statement to be executed

Field: sets the delimited string between each field

Record: sets the delimited string between each record

Rows: specify how many lines to print after printing a log, you can better observe the progress (default is 1000000)

File: sets the file name of the exported data (e.g. sqluldrData.txt)

Log: setting the generated log file

Text: sets the file type for output

Charset: sets the character set for output

Ncharset: sets the character set for output

Support to export data in batches according to different types, which is achieved by a parameter batch

The default batch is 500000 records, if you do not specify

A batch of 2 means 1 million records for one file.

The default value of this option is 0, which means that multiple files are not generated.

Field delimiter

Whether head prints the title. Default is NO.

You can try to load data into the database using a control file exported by ociuldr

-

3.sqlldr imports text files into the database

3.1 Overview

Sqlldr is a tool that comes with oracle to import text files into the database.

[root@oracle@zsh bin] # cd / u01/app/oracle/product/11.2.0/dbhome_1/bin

[root@oracle@zsh bin] # ll sql*

-rwxr-x--x 1 oracle oinstall 1362228 Nov 28 23:37 sqlldr

-rwxr-x--- 1 oracle oinstall 0 Aug 13 2009 sqlldrO

-rwxr-x--x 1 oracle oinstall 6889 Nov 28 23:37 sqlplus

The sqlldr command is simple: sqlldr scott/tiger control=/tmp/emp2.ctl log=emp2.log

Sqlldr related files: (1) text files, data to be imported

(2) ctl control file, indicating the imported text, the table information imported, and the import method

(3) log log file to record the import process

(4) error data file .bad and failure file .dsc (may not be specified)

Control file of sqlldr

* .ctl format is:

Load

Infile "d://test.txt" external data file

Infile "d://test1.txt" can specify multiple data files (through multiple lines of infile statements)

Append into table test appends data to the table

"fields terminated by", "data from external files are separated by", "

Fill in null values when fields in the trailing nullcols table do not have corresponding values

(

Id integer external. Integer external means the inserted data is string. If only integer is retained, the inserted data is binary.

Name "upper (: name)", which converts the inserted value to uppercase

Con ": id | |: name". The value of the CON column in the table is a combination of ID and NAME.

Dt date "yyyy-mm-dd" inserts date data

)

You can also use one of the following values in the location of the append:

Insert inserts values into the table, but requires the table to be empty at the beginning

Data in the replace delete table, and then insert the new value

Append appends data to the table

Truncate trunctate, and then insert the new value

You can also import data according to the position of the characters

The data for test.txt are as follows

11pr addpr pr 2007-07-8

127bd, 2008-07-8

1314, FCD, 2009-07-8

(id position (1:2)

Name position (4:6)

Dt date "yyyy-mm-dd" position (9:17)-to be verified

)

3.2 cases 1 simple

C:\ Users\ wzj > sqlldr scott/tiger control=d:/dept.ctl

The dept.ctl is as follows:

Load

Infile "d://test.txt"

Append into table tt

Fields terminated by ","

Trailing nullcols

(

Id integer external

Name "upper (: name)"

Con ": id | |: name"

Dt date "yyyy-mm-dd"

)

The data for test.txt are as follows

1pyrrine, 2007-07-8

2recoveryb pencils 2008-07-8

3Perspective.cPercent2009-07-8.

SQL > desc tt

Is the name empty? Types

--

ID VARCHAR2 (10)

NAME VARCHAR2 (20)

CON VARCHAR2 (30)

DT DATE

3.3 case 2 need to convert data type

ID CONSTANT "100"

DT "TRUNC (SYSDATE)"

BEGTIME date 'yyyy-mm-dd hh34:mi:ss'

ENDTIME date 'yyyy-mm-dd hh34:mi:ss'

ETL_TIME TIMESTAMP (6) 'yyyy-mm-dd hh34:mi:ss.ff6'

3.4 case 3 needs to skip some lines

-- dance SKIP 5 from the start

Payment.txt

BEG

1 | | 1020100 | | 02 | | 20120828001 | | CTIJ65002412000007 | | 375409794 | | 01 | | 10000 |

2 | | 1020100 | | 02 | | 20120828001 | | CTIJ65002412000006 | | 375409794 | | 01 | | 10001 |

3 | | 1020100 | | 02 | | 20120828001 | | CTIJ65002412000008 | | 375409795 | | 01 | | 10002 |

END

Load data

Infile 'payment.txt'

Append into table tmp_cx_autoagree_ids

When (01) 'BEG' and (01)' END'

-- when (1:3) 'BEG' AND (1:3)' END'

Fields terminated by'| |'

Trailing nullcols

More than 4 txt in 3.5 cases

-- the yugu_00000000000001.txt structure is as follows:

Policy number | insured × × × | Branch | Central Branch | Insurance type | liability subcode | Phase | Frequency | amount | Channel | Survival payment | pre-distribution | Source system | payment date |

010111300053960 | 372928xxxxxx225626 | 000000000001 | 00000000103 | 28950100 | 200 | 00 | 1 | 2 | 804.0 | 31 | 1 | 1 | APP0001 | 2015-01-21 |

010111300053960 | 372928xxxxxxxx25626 | 000000000001 | 00000000103 | 28950100 | 200 | 00 | 2 | 2 | 804.0 | 31 | 1 | APP0001 | 2016-01-21 |

-- Table bq_prepay_detail_yugu is structured as follows:

YEARMTH VARCHAR2 (10) Y due date

ORGCODE1 VARCHAR2 (24) Y Branch

Middle branch of ORGCODE2 VARCHAR2 (24) Y

POLICYNO VARCHAR2 (20) Y Policy No.

CLASSCODE VARCHAR2 (8) Y insurance

SALEATTR VARCHAR2 (10) Y Channel

DELCODE VARCHAR2 (3) Y payment type

DELNUM NUMBER (20) Y session

TYPENO VARCHAR2 (2) Y responsibility subcode

GUEST_ID VARCHAR2 (100) Y customer ID

JFTYPE VARCHAR2 (20) Y

AMT NUMBER (20pence 2) Y payment amount

ORGCODE3 VARCHAR2 (24) Y Branch

Whether ISSCJ VARCHAR2 (2) Y is a Survival Allowance

Whether ISPF VARCHAR2 (2) Y will be distributed in the future

-- Control file writing: bq_prepay_detail_yugu1446.ctl

Load data

Infile 'yugu_00000000000001.txt'

Infile 'yugu_00000000000002.txt'

Infile 'yugu_00000000000003.txt'

Infile 'yugu_00000000000004.txt'

Infile 'yugu_00000000000005.txt'

Infile 'yugu_00000000000006.txt'

Infile 'yugu_00000000000007.txt'

Infile 'yugu_00000000000008.txt'

Infile 'yugu_00000000000009.txt'

Infile 'yugu_00000000000010.txt'

Infile 'yugu_00000000000011.txt'

Infile 'yugu_00000000000012.txt'

Infile 'yugu_00000000000013.txt'

Infile 'yugu_00000000000014.txt'

Infile 'yugu_00000000000015.txt'

Infile 'yugu_00000000000016.txt'

Infile 'yugu_00000000000017.txt'

Infile 'yugu_00000000000018.txt'

Infile 'yugu_00000000000019.txt'

Infile 'yugu_00000000000020.txt'

Infile 'yugu_00000000000021.txt'

Infile 'yugu_00000000000022.txt'

Infile 'yugu_00000000000023.txt'

Infile 'yugu_00000000000024.txt'

Infile 'yugu_00000000000025.txt'

Infile 'yugu_00000000000026.txt'

Infile 'yugu_00000000000027.txt'

Infile 'yugu_00000000000028.txt'

Infile 'yugu_00000000000029.txt'

Infile 'yugu_00000000000030.txt'

Infile 'yugu_00000000000031.txt'

Infile 'yugu_00000000000032.txt'

Truncate into TABLE bq_prepay_detail_yugu

Fields terminated by Xero7c'

TRAILING NULLCOLS

(

POLICYNO

GUEST_ID

ORGCODE1

ORGCODE2

CLASSCODE

DELCODE

TYPENO

DELNUM

JFTYPE

AMT

SALEATTR

ISSCJ

ISPF

Orgcode3

YEARMTH "replace (: YEARMTH,'/','')"

)

Command:

Sqlldr bqtj/bqstatistics@FT CONTROL=bq_prepay_detail_yugu1446.ctl LOG=1446.LOG rows=1000 direct=y

4. Table export and guide examples

Create table PAETL.C02_RELPAYRC

(

SNO INTEGER

TYPEID INTEGER

CON_ID INTEGER

POLICYNO VARCHAR2 (20)

CLASSCODE VARCHAR2 (8)

CHECK_ID INTEGER

CHECKNO VARCHAR2 (40)

AMT NUMBER (20pd4)

CURRENCY CHAR (3)

GENDATE DATE

DELFRM CHAR (2)

PAYCODE CHAR (1)

MOVEWHY VARCHAR2 (120)

DELCODE CHAR (3)

TYPENO CHAR (2)

REGDATE DATE

AGENT_ID INTEGER

AGENTNO VARCHAR2 (20)

OPER_ID INTEGER

OPERNO VARCHAR2 (20)

DEPTNO VARCHAR2 (20)

DOCPRO VARCHAR2 (20)

DOCNO VARCHAR2 (20)

PERSON_ID INTEGER

PID VARCHAR2 (40)

IDTYPE CHAR (2)

PAYTIME INTEGER

BEGTIME DATE

ENDTIME DATE

BRANCH CHAR (14)

ETL_TIME TIMESTAMP (6)

SRC_SYS VARCHAR2 (20)

DEL_DATE CHAR (8)

REG_CODE VARCHAR2 (20)

DEL_TYPE INTEGER

SOUR_SYS VARCHAR2 (20)

FGSNO CHAR (3)

EMPNO CHAR (8)

SECKEY VARCHAR2 (20)

LOAD_TIME TIMESTAMP (6)

CONTNO CHAR (8)

SALE_MODE CHAR (4)

THE_THRDPRT CHAR (4)

CSRNO VARCHAR2 (20)

JOB CHAR (8)

BUSI_BRANCH CHAR (14)

INSRNC_RATE NUMBER (7 and 4)

POLICY_NO_TYPE VARCHAR2 (1)

FIN_PROC_TYPE INTEGER

SETNO VARCHAR2 (20)

)

-- bat that leads to txt

Cd D:\ wgw\ Tool

Ociuldr.exe user=paetl/paetl0203@LIFEPF_ODB query= "SELECT * FROM C02_RELPAYRC T WHERE T.REGDATE > = to_date ('20141001, June 20141001)" field= "|" file=.\ C02_RELPAYRC.txt log=.\ C02_RELPAYRC.log "

Exit

/

Sqlldr "paetl/*IK

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.