#1
22nd August 2014, 11:58 AM
| |||
| |||
Erpschools SQL Loader
Give me the information about the SQL Loader of Erpschools as soon as possible.
|
#2
22nd August 2014, 01:38 PM
| |||
| |||
Re: Erpschools SQL Loader
SQL*loader is one of the Oracle tool which is used to transfer the data from Flat-File to oracle Database table. We can find the following files in SQL*loader 1. Flat or Data File 2. Control File 3. Bad File 4. Discard File 5. Log File The extension of control file is .ctl Control File Creation: Load data INFILE ‘Data File Path’ INSERT INTO ‘Table Name’ FIELD TERMINATED BY ‘,’ WHERE deptno = 10 TRAILING NULL COLS(column1 , empno column2, ename column3, deptno) Keywords: INFILE: INFILE keyword is used to specify location of the datafile or datafiles. INFILE * specifies that the data is found in the control file and not in an external file. INFILE ‘$FILE’, can be used to send the filepath and filename as a parameter when registered as a concurrent program. INFILE ‘/home/vision/kap/import2.csv’ specifies the filepath and the filename. INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE “APPS”.”BUDGET”, APPS refers to the Schema and BUDGET is the Table name. FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc) OPTIONALLY ENCLOSED BY ‘”‘ specifies that data fields may also be enclosed by quotation marks. TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter. SKIP = n – Number of logical records to skip (Default 0) LOAD = n – Number of logical records to load (Default all) ERRORS = n – Number of errors to allow (Default 50) ROWS = n – Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all) BINDSIZE = n – Size of conventional path bind array in bytes (System-dependent default) SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run (header, feedback, errors, discards, partitions, all) DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE) PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE) SQL* Loader Modes: TYPE OF LOADING: INSERT — If the table you are loading is empty, INSERT can be used. APPEND — If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn’t already exist, the new rows are simply loaded. REPLACE — All rows in the table are deleted and the new data is loaded TRUNCATE — SQL*Loader uses the SQL TRUNCATE command. C:> sqlldr userid/passward@Database control=text1.ctl path=direct SQL* Loader Paths: We can execution SQL* loader in two paths or nodes Direct Conventional By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax C:> sqlldr userid/passward@Database control=text1.ctl path=direct Direct mode will disable the table and column constrains and it will insert the data. Conventional path will check every constrains, if it is satisfied it will insert the record Conventional path is just like ‘insert statement’ SQL Commands Limitations:to_date, to_char, upper, lower, Initcap, string, decode, nvl when clause sequence_name.next_value, Ref-Cursor sysdate, ltrim, rtrim, constant Structure of a Control file: Sample CTL file for loading a Variable record data file: OPTIONS (SKIP = 1) –The first row in the data file is skipped without loading LOAD DATA INFILE ‘$FILE’ — Specify the data file path and name APPEND — type of loading (INSERT, APPEND, REPLACE, TRUNCATE INTO TABLE “APPS”.”BUDGET” — the table to be loaded into FIELDS TERMINATED BY ‘|’ — Specify the delimiter if variable format datafile OPTIONALLY ENCLOSED BY ‘”‘ –the values of the data fields may be enclosed in “ TRAILING NULLCOLS — columns that are not present in the record treated as null (ITEM_NUMBER “TRIM(:ITEM_NUMBER)”, — Can use all SQL functions on columns QTY DECIMAL EXTERNAL, REVENUE DECIMAL EXTERNAL, EXT_COST DECIMAL EXTERNAL TERMINATED BY WHITESPACE “(TRIM(:EXT_COST))” , MONTH “to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),’DD-MON-YY’)” , DIVISION_CODE CONSTANT “AUD” — Can specify constant value instead of Getting value from datafile Contact: India Phone Number: +91 9000 333 598 USA Phone Number: +1 (847)-857-7655 |