Create external tables and load data in ORACLE

Create external tables and load data in ORACLE. external table feature data files are stored outside the operating system and are formatted and segmented. oracle External tables access data in data files through SQL. The data does not need to be loaded into the database and the data is readable. Therefore, you do not need to perform dml operations to create an index. step. create a directory object, which limits the data required by Server B. prepare a data file. The data file must be in a fixed format outside the operating system and cannot contain the Title c. create external table fields. If special fields need to be enclosed in double quotation marks "SYS_ID #" d. to delete External tables and directories, delete the table before deleting directory 3. create external table real-column creation directory: Create or replace directory SQLDR as 'd: \ oracle \ oradata \ data; create external table: (generate logs using sqlldr) -- Create tablecreate table SYS_SQLLDR_X_EXT _ MUREX_GL_TEMP (areano VARCHAR2 (20), currency VARCHAR2 (20), apcode VARCHAR2 (20), orgcde VARCHAR2 (20), damount VARCHAR2 (200), camount VARCHAR2 (200 ), remakr VARCHAR2 (200) organization external (type ORACLE_LOADER default directory SQLDR access parameters (records delimited by 0X '0a' BADFILE 'sqldr': 'murex _ upload' DISCARDFILE 'sqldr ': 'murex _ GLRCN_INIT_disfile.dat 'logfile' MUREX _ gl_t Emp. log_xt 'readsize 1048576 fields terminated by "|" optionally enclosed by ''ldrtrim reject rows with all null fields (" AREANO "CHAR (255)," CURRENCY "CHAR (255 ), "APCODE" CHAR (255), "ORGCDE" CHAR (255), "DAMOUNT" CHAR (255), "CAMOUNT" CHAR (255 ), "REMAKR" CHAR (255) location (SQLDR: 'murex _ GLRCN_INIT_20190831.dat ') reject limit UNLIMITED; 4. ddl statements used by sqlldr to generate external tables run sqlldr in the command (the control file must be prepared in advance):. use sq Lldr generates the log file sqlldr user_name/password @ oradb control = control_file.ctl external_table = gernerate_only; NOT_USED: default value. EXECUTE: This value indicates that SQLLDR does not generate and EXECUTE an SQLINSERT statement. Instead, it creates an External table and uses a batch SQL statement for loading. GENERATE_ONLY: So that SQLLDR does not load any data, but only generates the executed SQL DDL and DML statements and stores them in the log files it creates. Note: DIRECT = TRUE overwrites EXTENAL_TABLE = GENERATE_ONLY. If DIRECT = TRUE is specified, data is loaded without generating an External table. D: \ oracle \ oradata \ data> sqlldr boc_rwa3/a @ rwadb control = murex_gl_temp.ctl external_table = generate_only SQL * Loader: Release 11.2.0.3.0-Production on Thursday 22:27:03 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. b. extract ddl statement create table "SYS_SQLLDR_X_EXT_MUREX_GL_TEMP" ("AREANO" VARCHAR2 (20), "CURRENCY" VARCHAR2 (20), "APCODE" VARCHAR2 (20 ), "ORGCDE" VARCH AR2 (20), "DAMOUNT" VARCHAR2 (200), "CAMOUNT" VARCHAR2 (200), "REMAKR" VARCHAR2 (200 )) ORGANIZATION external (TYPE oracle_loader -- specifies the access method of the external table, 9i does not support oracle_datapump default directory sqldr -- directory access parameters -- configure external table PARAMETERS (records delimited by newline characterset ZHS16GBK -- Record ended BADFILE 'sqldr' with a different behavior ': 'murex _ GLRCN_INIT_20130809_badfile.dat '-- stores the description of the record file for processing failure DISCARDFILE 'sqldr': 'murex _ GLRCN_INIT_20130809_d Isfile. dat '-- stores the LOGFILE description for processing discarded records 'murex _ gl_temp.log_xt' -- Log File READSIZE 1048576 -- the default buffer used by Oracle to read input data files. MB is used here, for example, the dedicated mode is allocated from PGA. For example, the shared mode is assigned from sga fields terminated by "|" optionally enclosed by "" LDRTRIM -- "|" The Terminator of the description field, "" role character reject rows with all null fields -- all rows with null values are skipped and recorded in bad file (--- The following is the definition of each column in the external file "AREANO" CHAR (255) terminated by "|" optionally enclosed by "", "CURRENCY" CHAR (255) TERMINAT Ed by "|" optionally enclosed by "", "APCODE" CHAR (255) terminated by "|" optionally enclosed by "", "ORGCDE" CHAR (255) terminated by "|" optionally enclosed by "", "DAMOUNT" CHAR (255) terminated by "|" optionally enclosed by "", "CAMOUNT" CHAR (255) terminated by "|" optionally enclosed by "", "REMAKR" CHAR (255) terminated by "|" optionally enclosed by ") location ('murex _ GLRCN_INIT_201 30809. dat '-- Description of the external file name) reject limit unlimited -- Description of the number of allowed errors, which is not limited here; a line break is prompted here: Because when dealing with this problem, the problem is found only after one day. The windows line feed is \ r \ n, the hexadecimal value is: 0D0ALINUX line feed is \ n, and The hexadecimal value is: 0A. Therefore, we confirm that the delimiter should be checked with UE first, and then the statement for creating the External table will be modified. Otherwise, the load will fail.. use UE to view the source file (before hexadecimal) B. use UE to view the source file (after hexadecimal) because the source file line break is 0A, the line break should be changed to: records delimited by 0X '0a' -- 0X to indicate that the sixteen characters are in progress, and 0A is a linux line break. therefore, this method is applicable to identifying line breaks. 5. key Points of External table definition. the ORGANIZATIONEXTERNAL keyword, which must exist. To indicate that the defined table is an external table. B. important parameter External table Type ORACLE_LOADER: defines the default mode for external tables, and can only be read-only to load text data. ORACLE_DATAPUMP: supports data loading and unloading. The data file must be a binary dump file. You can extract data from an external table and load it to an internal table. You can also detach the data from an internal table and fill it in as a binary file to an external table. C. DEFAULTDIRECTORY: the default directory specifies the path of the external file d. LOCATION: defines the LOCATION of the External table. access parameters: describes how to ACCESS the RECORDS keyword of an External table and then defines how to identify the data row DELIMITEDBY 'xxx' -- line break. newline is often used to define line breaks and specify the character set. Special characters need to be defined separately. For example, special characters can use OX 'Sixteen bits value'. For example, if the hexadecimal character of tab (/t) is 9, DELIMITEDBY0X '09 '; the hexadecimal value of cr (/r) is d, which means DELIMITEDBY0X '0d '. Skip x -- skip x rows of data. In some files, the first row is the column name. to SKIP the first row, use SKIP 1. The FIELDS keyword defines how to identify a field. Commonly Used: FIELDS: TERMINATEDBY 'X' -- field delimiter. ENCLOSEDBY 'X' -- a field reference character. All data contained in this symbol is treated as a field. For example, a data row is in the format of "abc", "a" "B," "c ,". After using the TERMINATEDBY parameter, 'enablesdby', the system will read two fields. The value of the first field is abc, and the value of the second field is a "B," c ,. LRTRIM -- Delete the first and last spaces. Missing fieldvaluesarenull -- some field vacancy values are set to NULL. If the field length and delimiter are uncertain and are to be used as an external table file, you can use UltraEdit and Editplus for analysis and testing. If the file size is large, you need to split the file into small files and extract data from them for testing. External table error handling reject limit unlimited when creating an external table, add the LIMIT clause to indicate the number of errors allowed. The default value is zero. If it is set to UNLIMITED, the error is unrestricted. The BADFILE and NOBADFILE clauses are used to specify the file to which the captured Conversion error is stored. If NOBADFILE is specified, errors during conversion are ignored. If this parameter is not specified, the system automatically generates an External table with the same name in the source directory. the bad file badfile records the results of this operation. The next time it will be overwritten, the LOGFILE and NOLOGFILE clauses will also add LOGFILE 'Log _ FILE to the accessparameters. then, all Oracle error messages are placed in 'Log _ FILE. the NOLOGFILE clause indicates that no error information is recorded in the log. If this clause is ignored, the system automatically generates an External table with the same name in the source directory. LOG File