ORACLE SQL* LOADER:

SQL* LOADER:

SQL*Loader is a tool that is used to load data into a database, it is very powerful and has the followingcapabilities· use it to transform data before it's loaded into the database· load data from multiple sources

i.e. disk, tape and named pipes· load data across a network· selectively load from the input file based on conditions· load part or all of a table· perform simultaneous loads· auto the whole process using the Oracle scheduler.doc· load complex object-relational data

The SQL*Loader can perform several types of data loading·

conventional - reads multiple rows at the same time into a bind array then inserts all at once and thencommits them, basically using insert statements· direct-path - does not use insert statements, basically loads the data blocks above the high water markthen adjusts it after it has finished.

· external - the new external tables feature uses SQL*Loader to access external data as if it were part ofthe database tables.direct-path loading is much faster than conventional loading as it bypasses the Oracle SQL mechanism,however there are few options available when using this option.

Direct-load has the following criteria· loads data directly into the data block en mass by passing the buffer cache and redo and undo logs· indexes are rebuild after loading· foreign keys and check constraints are disabled during loading (automatically disabled then re-enabled)· primary, unique keys and null keys are enabled during loading· no activity is allowed on the table as it is locked· triggers do not fire· clusters are not supported· foreign keys are disabled when loading· loading parent and child tables together is not supported· loading varray or bfile columns is not supported·

you cannot apply SQL functions during loadingUse the below as a guideThere are two steps when using the SQL*Loader select the data that you want to load and create a SLQ*Loadercontrol fileSQL*Loader control fileThis is a simple text file which has specific details about the data load job, such has location on the datafile.

There are many option, i have listed a number below but its best to read the Oracle documentationIf your data is already formatted you can use one of the file format parameters· stream record format - uses the record terminator to indicate the end of the record, this is the slowest ofthe methods· variable record format - you explicity specify the length at the beginning of the record· fixed record format - you specify that all records are a specific fixed sizeInvoking

SQL*LoaderYou can either use a parameter file or specify all the parameters on the command line