What are the file types available in SQL Loader?

What are the file types available in SQL Loader?

This release of SQL*Loader supports loading of four LOB types:

• BLOB: a LOB containing unstructured binary data.
• CLOB: a LOB containing character data.
• NCLOB: a LOB containing characters in a database national character set.
• BFILE: a BLOB stored outside of the database tablespaces in a server-side operating system file.

How do you create a control file?

Complete the following steps to create a new control file.

1. Make a list of all datafiles and redo log files of the database.
2. Shut down the database.
3. Back up all datafiles and redo log files of the database.
4. Start up a new instance, but do not mount or open the database: STARTUP NOMOUNT.

What is log file in SQL Loader?

The log file contains a detailed summary of the load. Most of the log file entries are records of successful SQL*Loader execution. However, errors can also cause log file entries. For example, errors found during parsing of the control file appear in the log file.

What is a control file in Oracle database?

Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes: The database name. Names and locations of associated datafiles and online redo log files. The current log sequence number.

How do I recreate a control file?

SQL> alter database backup controlfile to trace; A trace file will be generated in the user_dump_destination directory. After navigating to the directory locate the latest trace file by date/time by issuing “ls -ltr” command. Modify the trace file and use it as a script to create the control.

What is the character set of SQL loader control file?

Control File Character Set The SQL*Loader control file itself is assumed to be in the character set specified for your session by the NLS_LANGparameter. If the control file character set is different from the datafile character set, keep the following issue in mind.

How does this control file load the data in SQL*loader?

This control file loads Accounting as department number 1, Personnel as 2, and Manufacturing as 3. The sequence numbers are then incremented for the next record, so Shipping loads as 4, Purchasing as 5, and so on. SQL*Loader Datatypes

What is the default name for a discard file in SQL*loader?

Because the DISCARDMAXoption is used, SQL*Loader assumes that a discard file is required and creates it with the default name mydat4.dsc. Identifying Data in the Control File with BEGINDATA If the data is included in the control file itself, then the INFILEclause is followed by an asterisk rather than a filename.

How do I load multiple datafiles in SQL loader?

Specifying Multiple Datafiles To load data from multiple datafiles in one SQL*Loader run, use an INFILEstatement for each datafile. Datafiles need not have the same file processing options, although the layout of the records must be identical.