Using SQL * Loader to create an external table

The experiment in Using SQL * Loader to create an external table contains a txt text file. Based on this text file, use SQL * Loader to create an External Table.1, create a control file [oracle @ vmoel5u4 ~] $ Vi car. control load datainfile 'car.txt 'badfile' car. bad 'discardfile' car. discard 'appendinto table car_info_testFIELDS terminated by "," trailing nullcols (maker, model, no_cyl, first_built_date date 'yyyy/mm/dd', engine, hp, price) 2, create an External table according to the control file [oracle @ vmoel5u4 ~] $ Sqlldr oltp_usr/oracle control = car. control external_table = GENERATE_ONLY log = cardata. logSQL * Loader: Release 10.2.0.1.0-Production on Sun Mar 31 19:05:06 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. 3. Use cardata. log File to view the syntax for creating an external table: [oracle @ vmoel5u4 ~] $ Vi cardata. log "PRICE" CHAR (255) terminated by ",") Data File: car.txt Bad File: car. bad Discard File: car. discard (Allow all discards) Number to load: ALLNumber to skip: 0 Errors allowed: 50 Continuation: none specifiedPath used: External TableTable CAR, loaded from every logical record. insert option in effect for this table: appendtrailing nullcols option in effect Column Name Position Len Term En Cl Datatype detail ---------- ----- ---- character maker first *, charactermodel next *, CHARACTERNO_CYL NEXT *, CHARACTERFIRST_BUILT_DATE NEXT *, DATE yyyy/mm/ddENGINE NEXT *, characterhp next *, characterprice next *, character create table statement for external table: ---------------------------------------------------------------------- CREATE TAB LE "SYS_SQLLDR_X_EXT_CAR" ("MAKER" VARCHAR2 (20), "MODEL" VARCHAR2 (20), "NO_CYL" NUMBER, "FIRST_BUILT_DATE" DATE, "ENGINE" VARCHAR2 (20 ), "HP" NUMBER (10, 1), "PRICE" NUMBER (10, 2) ORGANIZATION external (TYPE oracle_loader default directory test access parameters (records delimited by newline characterset US7ASCII BADFILE 'test ': 'Car. bad 'discardfile 'test': 'Car. discard 'logfile' cardata. log_xt 'reads IZE 1048576 fields terminated by "," ldrtrim missing field values are null reject rows with all null fields ("MAKER" CHAR (255) terminated ",", "MODEL" CHAR (255) terminated by ",", "NO_CYL" CHAR (255) terminated by ",", "FIRST_BUILT_DATE" CHAR (255) terminated ", "DATE_FORMAT date mask" yyyy/mm/dd "," ENGINE "CHAR (255) terminated by", "," HP "CHAR (255) terminated ",", "PRICE" CHAR (255) TERMI Nated by ",") location ('car.txt ') reject limit unlimited insert statements used to load internal tables: tables [oracle @ vmoel5u4 ~] $ Vi cardata. log terminated by ",", "PRICE" CHAR (255) terminated by ",") location ('car.txt ') reject limit unlimited insert statements used to load internal tables: into insert/* + append */into car (MAKER, MODEL, NO_CYL, FIRST_BUILT_DATE, ENGINE, HP, PRICE) SELECT "MAKER", "MODEL", "NO_CYL ", "FIRST_BUILT_DATE", "ENGINE", "HP", "PRICE" FROM "SYS_SQLLDR_X_EXT_CAR" statements to cleanup objects created by previous statements: Dropping drop table "SYS_SQLLDR_X_EXT_CAR" by cardata above. the log file information shows that the syntax for creating an external table is complete. You can create an external table with only a slight modification. 4, create an External table SQL> conn hr/hrConnected. create table hr. car_info_test ("MAKER" VARCHAR2 (20), "MODEL" VARCHAR2 (20), "NO_CYL" NUMBER, "FIRST_BUILT_DATE" DATE, "ENGINE" VARCHAR2 (20 ), "HP" NUMBER (10, 1), "PRICE" NUMBER (10, 2) ORGANIZATION external (TYPE oracle_loader default directory test access parameters (records delimited by newline characterset US7ASCII BADFILE 'test ': 'Car. bad 'discardfile 'test': 'Car. discard 'logfile' cardata. log_xt 'readsize 1048576 fields terminated by "," ldrtrim missing field values are null reject rows with all null fields ("MAKER" CHAR (255) terminated ",", "MODEL" CHAR (255) terminated by ",", "NO_CYL" CHAR (255) terminated by ",", "FIRST_BUILT_DATE" CHAR (255) terminated ", "DATE_FORMAT date mask" yyyy/mm/dd "," ENGINE "CHAR (255) terminated by", "," HP "CHAR (255) terminated ",", "PRICE" CHAR (255) terminated by ",") location ('car.txt ') reject limit unlimited; Table created.5, check whether the External Table is created successfully. SQL> select count (*) from car_info_test; COUNT (*) ---------- 17