trailing nulcols:difference between external table and sqlldr:create external table from controlfile

the table definiton :

SQL> desc tt;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(1)
 B                                                  VARCHAR2(20)
 C                                                  NUMBER
 D                                                  NUMBER
 E                                                  NUMBER
 F                                                  VARCHAR2(20)

the content of ldr.dat file :

ocm [oracle@/home/oracle/ldr ]$ cat ldr.dat
P,James,31,
P,Thomas,22,
E,Pat,38,93645,1122,Engineering,
P,Bill,19,
P,Scott,55,
S,Judy,45,27316,English,
S,Karen,34,80356,History,
E,Karen,61,90056,1323,Manufacturing,
S,Pat,29,98625,Spanish,
S,Cody,22,99743,Math,
P,Ted,43,
E,Judy,44,87616,1544,Accounting,
E,Bob,50,63421,1314,Shipping,
S,Bob,32,67420,Psychology,
E,Cody,33,25143,1002,"Human Resources",

create table ldr_ext(a varchar2(1) ,b varchar2(50) ,c number , d number, e number, f varchar2(50))
  organization external
  ( type oracle_loader
    default directory ldr_dir
    access parameters
    ( records delimited by newline
      badfile ldr_dir:'ldr_%a_%p.bad'
      logfile ldr_dir:'ldr_%a_%p.log'
      fields terminated by ',' optionally enclosed by '"' missing field values are null
      ( a, b, c, d ,e, f )
    ) location ('ldr.dat')
  )  reject limit unlimited

/

[directory object name:] filename

This clause is used to specify the name of an output file (BADFILE, DISCARDFILE, or LOGFILE). The directory object name is the name of a directory object where the user accessing the external table has privileges to write. If the directory object name is omitted, then the value specified for the DEFAULT DIRECTORY clause in the CREATE TABLE...ORGANIZATION EXTERNAL statement is used.

The filename parameter is the name of the file to create in the directory object. The access driver does some symbol substitution to help make filenames unique in parallel loads. The symbol substitutions supported for UNIX and Windows NT are as follows (other platforms may have different symbols):

• %p is replaced by the process ID of the current process. For example, if the process ID of the access driver is 12345, then exttab_%p.log becomes exttab_12345.log.

• %a is replaced by the agent number of the current process. The agent number is the unique number assigned to each parallel process accessing the external table. This number is padded to the left with zeros to fill three characters. For example, if the third parallel agent is creating a file and bad_data_%a.bad was specified as the filename, then the agent would create a file named bad_data_003.bad.

• %% is replaced by %. If there is a need to have a percent sign in the filename, then this symbol substitution is used.

If the % character is encountered followed by anything other than one of the preceding characters, then an error is returned.

If %p or %a is not used to create unique filenames for output files and an external table is being accessed in parallel, then output files may be corrupted or agents may be unable to write to the files.

If you specify BADFILE (or DISCARDFILE or LOGFILE), you must specify a filename for it or you will receive an error. However, if you do not specify BADFILE (or DISCARDFILE or LOGFILE), then the access driver uses the name of the table followed by _%p as the name of the file. If no extension is supplied for the file, a default extension will be used. For bad files, the default extension is .bad; for discard files, the default is .dsc; and for log files, the default is .log.

load data
infile '/home/oracle/ldr/ldr.dat'
badfile '/home/oracle/ldr/new.bad'
truncate into table tt
fields terminated by ',' optionally enclosed by '"'  trailing nullcols
(a,b,c,d,e,f)

Now assume the sqlldr control file exists as upper and works very well , how can I create a external table in the fastest way?

so if there is a grid control , everything can generated automatically

 sqlldr qdl/oracle control=ldr.ctf external_table=generate_only

prod [oracle@/home/oracle/ldr ] $ cat ldr.log

SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 3 08:34:43 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   ldr.ctf
Data File:      /home/oracle/ldr/ldr.dat
  Bad File:     /home/oracle/ldr/ldr.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table TT, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,  O(") CHARACTER
B                                    NEXT     *   ,  O(") CHARACTER
C                                    NEXT     *   ,  O(") CHARACTER
D                                    NEXT     *   ,  O(") CHARACTER
E                                    NEXT     *   ,  O(") CHARACTER
F                                    NEXT     *   ,  O(") CHARACTER

 

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/ldr/'

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_TT"
(
  "A" VARCHAR2(1),
  "B" VARCHAR2(20),
  "C" NUMBER,
  "D" NUMBER,
  "E" NUMBER,
  "F" VARCHAR2(20)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ldr.bad'
    LOGFILE 'ldr.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "A" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "B" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "C" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "D" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "E" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "F" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'ldr.dat'
  )
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO TT
(
  A,
  B,
  C,
  D,
  E,
  F
)
SELECT
  "A",
  "B",
  "C",
  "D",
  "E",
  "F"
FROM "SYS_SQLLDR_X_EXT_TT"

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_TT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

 

Run began on Fri May 03 08:34:43 2013
Run ended on Fri May 03 08:34:43 2013

Elapsed time was:     00:00:00.07
CPU time was:         00:00:00.03

 

 

 

some examples for oracle_loader external table in oracle document :

Example: External Table with Terminating Delimiters

The following is an example of an external table that uses terminating delimiters. It is followed by a sample of the datafile that can be used to load it.

CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY WHITESPACE)
                         LOCATION ('info.dat'));

Alvin Tolliver 1976
Kenneth Baer 1963
Mary Dube 1973

Example: External Table with Enclosure and Terminator Delimiters

The following is an example of an external table that uses both enclosure and terminator delimiters. Remember that all whitespace between a terminating string and the first enclosure string is ignored, as is all whitespace between a second enclosing delimiter and the terminator. The example is followed by a sample of the datafile that can be used to load it.

CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4)) 
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                        ACCESS PARAMETERS (FIELDS TERMINATED BY "," ENCLOSED BY "("  AND ")")
                        LOCATION ('info.dat'));

(Alvin) ,   (Tolliver),(1976)
(Kenneth),  (Baer) ,(1963)
(Mary),(Dube) ,   (1973)

Example: External Table with Optional Enclosure Delimiters

The following is an example of an external table that uses optional enclosure delimiters. Note that LRTRIM is used to trim leading and trailing blanks from fields. The example is followed by a sample of the datafile that can be used to load it.

CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
                         ACCESS PARAMETERS (FIELDS TERMINATED BY ','
                                            OPTIONALLY ENCLOSED BY '(' and ')'
                                            LRTRIM)
                         LOCATION ('info.dat'));

Alvin ,   Tolliver , 1976
(Kenneth),  (Baer), (1963)
( Mary ), Dube ,    (1973)

SQL> CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );

file:///D:/B19306_01/server.102/b14215/et_params.htm#i1009499

[UNSIGNED] INTEGER [EXTERNAL] [(len)]

This clause defines a field as an integer. If EXTERNAL is specified, the number is a character string. If EXTERNAL is not specified, the number is a binary field. The valid values for len in binary integer fields are 1, 2, 4, and 8. If len is omitted for binary integers, the default value is whatever the value of sizeof(int) is on the platform where the access driver is running. Use of the DATA IS {BIG | LITTLE} ENDIAN clause may cause the data to be byte-swapped before it is stored.

If EXTERNAL is specified, then the value of len is the number of bytes or characters in the number (depending on the setting of the STRING SIZES ARE IN BYTES or CHARACTERS clause). If no length is specified, the default value is 255.

DECIMAL [EXTERNAL] and ZONED [EXTERNAL]

The DECIMAL clause is used to indicate that the field is a packed decimal number. The ZONED clause is used to indicate that the field is a zoned decimal number. The precision field indicates the number of digits in the number. The scale field is used to specify the location of the decimal point in the number. It is the number of digits to the right of the decimal point. If scale is omitted, a value of 0 is assumed.

Note that there are different encoding formats of zoned decimal numbers depending on whether the character set being used is EBCDIC-based or ASCII-based. If the language of the source data is EBCDIC, then the zoned decimal numbers in that file must match the EBCDIC encoding. If the language is ASCII-based, then the numbers must match the ASCII encoding.

If the EXTERNAL parameter is specified, then the data field is a character string whose length matches the precision of the field.