Bo's Oracle Station

查看: 2848|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-7-12 15:42:17 | 显示全部楼层 |阅读模式
Screenshot.png

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

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

第5个实验注意查询:
  1. select  * from emp;

  2. select  * from dept;

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

  6. --truncate table proj;

  7. --truncate table emp;

  8. ---
复制代码

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.


  1. select  * from dba_tables t
  2. where t.OWNER='HR' and t.TABLE_NAME='T_EXTERNAL';

  3. select  t.TABLE_NAME from dba_external_tables t
  4.   where t.OWNER='HR';
  5.   
  6.   ----
  7.   
  8.   select  * from dba_datapump_jobs;
  9.   
  10.   select * from system.my_full;
复制代码
  1. sqlldr hr/oracle_4U  control=ccb.ctl log=ccb.sql   external_table=GENERATE_ONLY
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 01:11 , Processed in 0.048632 second(s), 27 queries .

快速回复 返回顶部 返回列表