课程第52/53次（2017-07-11星期二和2017-07-13星期四）

botang · 发表于 2017-7-12 15:42:17

http://124.16.180.178:8080/studentguide_sec_O11g/1Z0-052-17/SQLLOADER/

游客，本帖隐藏的内容需要积分高于 100 才可浏览，您当前积分为 0

第5个实验注意查询：

select * from emp;
select * from dept;
--truncate table dept;
---777 888 999 294 40 425 102
select * from proj where empno in (1234, 2849 , 2664);
--truncate table proj;
--truncate table emp;
---

复制代码

OU17-29教材是以上TAR包中的第7个例子：
1    -- This is a sample control file
2 LOAD DATA
3 INFILE ’SAMPLE.DAT’
4 BADFILE ’sample.bad’
5 DISCARDFILE ’sample.dsc’
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = ’.’
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
      deptno POSITION(1:2) INTEGER EXTERNAL(2)
      ,
      job POSITION(7:14)
"UPPER(:job)",
      mgr POSITION(28:31) INTEGER EXTERNAL(4)
      ,
      ename POSITION(34:41) "UPPER(:ename)",
      empno POSITION(45) INTEGER EXTERNAL
      ,
      sal POSITION(51)
      "TO_NUMBER(:sal,’$99,999.99’)",
      comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
      ":comm * 100"
   )
The explanation of this sample control file (by line numbers) is as follows:
1. Comments can appear anywhere in the command section of the file, but they must not appear in the data. Precede any comment with two hyphens. All text to the right of the double hyphen is ignored until the end of the line.
2. The LOAD DATA statement indicates to SQL*Loader that this is the beginning of a new data load. If you are continuing a load that has been interrupted in progress, use the CONTINUE LOAD DATA statement.
3. The INFILE keyword specifies the name of a data file containing data that you want to load.
4. The BADFILE keyword specifies the name of a file into which rejected records are placed.
5. The DISCARDFILE keyword specifies the name of a file into which discarded records are placed.
6. The APPEND keyword is one of the options that you can use when loading data into a table that is not empty. To load data into a table that is empty, use the INSERT keyword.
7. The INTO TABLE keyword enables you to identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database.
8. The WHEN clause specifies one or more field conditions that each record must match before SQL*Loader loads the data. In this example, SQL*Loader loads the record only if the 57th character is a decimal point. That decimal point delimits dollars and cents in the field and causes records to be rejected if SAL has no value.
9. The TRAILING NULLCOLS clause prompts SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
10. The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.

select * from dba_tables t
where t.OWNER='HR' and t.TABLE_NAME='T_EXTERNAL';
select t.TABLE_NAME from dba_external_tables t
where t.OWNER='HR';
----
select * from dba_datapump_jobs;
select * from system.my_full;

复制代码

sqlldr hr/oracle_4U control=ccb.ctl log=ccb.sql external_table=GENERATE_ONLY

复制代码