SQL*LOADER

1. SQL*LOADER

2. INTRO….. • What is Sql*Loader? It is an utility provided by oracle t load data from external data files into an Oracle database.

3. Database files. • An Oracle database consists of one or more database files. • These files contain data that can be accessed with the help of SQL. • A file can be associated with one and only one tablespace. • The database when created initially, has only one physical file. • The maximum number of files that can be opened at a time are 32 by default. • One or more physical files form a logical unit called tablespace

4. FEW TERMS…. • Input data files. • Control files. • Sql loader. • Bad file. • Discard file. • Log files.

6. Input data files. • It contains the external data files which you want to load in your database. • Files can be stored on disk or tape. • Record formats can be of fixed or variable length….

7. Fixed length records.variable length records. • What are fixed length records? Fixed same length ,data type and position. • What are variable length records? Allocates as per requirement.

8. Control files. • Its functions- 1.Controls data. 2.Maps data file into oracle tables. 3.Storage of data. 4.Can define validation expression for the data. A database’s overall physical architecture is maintained by its control files. • A control file is associated with a single database • Control files are automatically modified by Oracle; a user cannot edit them.

9. Log files. A Redo Log contains all the transactions that have occurred against the database. • No activity in the database can take place without being recorded in Redo Logs. • The Redo Logs are necessary to protect against data loss. • Redo Logs are also called transaction logs. • Every database requires a minimum of two Redo Logs. • These are used in recovery operations to restore lost or damaged files.

10. Discard file. • When given condition is not met than that specific record is entered into discard file. • Ex-all records must have a value x in column 1. • Records not having x value will be written to the discard file.

11. Bad file BAD FILE. • First checks the format-if length is inconsitent, SQL*Loader writes that record as bad file. • Secondly checks the validation-checks constraints,datatype conversion errors

12. Example. Load data Infile info.txt insert/append Into table info.txt ( Id integer external terminate by ‘,’ , Note char terminate by ‘.’ );

13. Thank u….

14. Example. • This sample control file (loader.ctl) will load an external data file containing delimited data: • load data infile 'c:\data\mydata.csv' into table emp fields terminated by "," optionally enclosed by '"' ( empno, empname, sal, deptno ) . • The mydata.csv file may look like this: 10001,"Scott Tiger", 1000, 40 10002,"Frank Naude", 500, 20

15. Example to load variable length in oracle. LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS { data1, data2 } BEGINDATA 11111,AAAAAAAAAA 22222,"A,B,C,D,"

17. Example to load fixed length in oracle. • If you need to load positional data (fixed length), look at the following control file example: • LOAD DATA INFILE * INTO TABLE load_positional_data { data1 POSITION(1:15), data2 POSITION(6:15) } BEGINDATA 11111AAAAAAAAAA 22222BBBBBBBBBB