1 / 13

SQLLOADER

Marina G. Erechtchoukova. 2. What Is It?. Oracle utility which populates Oracle tables from host filesTables must be created firstSQL*LOADER operates with control fileData can be put into control file or stored in separate data file.. Marina G. Erechtchoukova. 3. Data . Positioned fileEach row s

shae
Download Presentation

SQLLOADER

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. SQL*LOADER

    2. Marina G. Erechtchoukova 2 What Is It? Oracle utility which populates Oracle tables from host files Tables must be created first SQL*LOADER operates with control file Data can be put into control file or stored in separate data file.

    3. Marina G. Erechtchoukova 3 Data Positioned file Each row starts from the most left position. Each field takes the number of positions according to its domain constraint Files with delimiter Each record is placed into one row Each field is delimited by a symbol

    4. Marina G. Erechtchoukova 4 Control file Specifies the action: Insert Replace Append Specifies the data file name and list of fields Has an extension “.ctl”

    5. Marina G. Erechtchoukova 5 Control File for Positioned Data File LOAD DATA INFILE File_name REPLACE INTO TABLE Table_name (field_1 POSITION (SP:EP) data_type_1, …, field_K POSITION (SP:EP) data_type_K) )

    6. Marina G. Erechtchoukova 6 Control File for Data File with Delimiter LOAD DATA INFILE File_name REPLACE INTO TABLE Table_name FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY “’” (field_1, field_2, …, field_K)

    7. Marina G. Erechtchoukova 7 Control File Contains Data Control file is prepared in the same way INFILE * BEGINDATA clause is added Below the clause data are placed according to the format declared in the control information

    8. Marina G. Erechtchoukova 8 Using the SQL*LOADER Create table Prepare Control file and data file or place data into control file SQL*LOADER is executed from the HOST environment

    9. Marina G. Erechtchoukova 9 Using the SQL*LOADER in ITEC Lab Login onto sit.yorku.ca Call SQL*LOADER utility sqlldr login@studb10g Control_File System asks for your password Enter your password

    10. Marina G. Erechtchoukova 10 If You Get an Error… Control file has an extension other than “.ctl” You did not create the table first You use different column names in the control file and in the table you created SQL*LOADER creates “.log” file SQL*LOADER creates “.bad” file if an error occurs.

    11. Marina G. Erechtchoukova 11 Example: Control File LOAD DATA INFILE 'site.dat' REPLACE INTO TABLE Site FIELDS TERMINATED By ',' OPTIONALLY ENCLOSED BY "'" (site_id, location)

    12. Marina G. Erechtchoukova 12 Example: Data File 1, 'Paris' 2, 'Boston' 3, 'London‘ 4, ‘Ottawa’ 5, ‘Toronto’

    13. Marina G. Erechtchoukova 13 Example: Data in the Control File LOAD DATA INFILE * REPLACE INTO TABLE building3 FIELDS TERMINATED BY '|' ( B_NAME, HF, DID, B_SIZE) BEGINDATA green|y|d3|20 red|y|d1|18 blue|n|d2|16

More Related