You can use SQL*Loader to bulk load objects, collections, and LOBs. It is assumed that you are familiar with the concept of objects and with Oracle’s implementation of object support.
Supported Object Types
SQL*Loader supports loading of the following two object types:
When a column of a table is of some object type, the objects in that column are referred to as column-objects. Conceptually such objects are stored in entirety in a single column position in a row. These objects do not have object identifiers and cannot be referenced.
These objects are stored in tables, known as object tables, that have columns corresponding to the attributes of the object. The object tables have an additional system-generated column, called SYS_NC_OID$, that stores system-generated unique identifiers (OIDs) for each of the objects in the table. Columns in other tables can refer to these objects by using the OIDs.
Supported Collection Types
SQL*Loader supports loading of the following two collection types:
A nested table is a table that appears as a column in another table. All operations that can be performed on other tables can also be performed on nested tables.
VARRAYs are variable sized arrays. An array is an ordered set of built-in types or objects, called elements. Each array element is of the same type and has an index, which is a number corresponding to the element’s position in the VARRAY. When creating a VARRAY type, you must specify the maximum size. Once you have declared a VARRAY type, it can be used as the datatype of a column of a relational table, as an object type attribute, or as a PL/SQL variable.
Supported LOB Types
A LOB is a large object type. This release of SQL*Loader supports loading of four LOB types:
LOBs can be column datatypes, and with the exception of the NCLOB, they can be an object’s attribute datatypes. LOBs can have an actual value, they can be NULL, or they can be "empty."
SQL*Loader DDL Behavior and Restrictions
In order to provide object support, the behavior of certain DDL clauses and certain restrictions is different starting with release 8.1.5. The following list describes these changes. The changes apply in all cases, not just when you are loading objects, collections, or LOBs.
If the field_condition is true, the DEFAULTIF clause initializes the LOB or collection to empty (not null). If the field_condition is true, the NULLIF clause initializes the LOB or collection to null, as it does for other datatypes.
You can chain field_condition arguments using the AND logical operator. Note the following:
In previous versions of SQL*Loader, you could load fields that were delimited (terminated or enclosed) by a character. Beginning with release 8.1.5, the delimiter can be one or more characters long. The syntax to specify delimited fields remains the same, except that you can specify entire strings of characters as delimiters.
As with single-character delimiters, when you specify string delimiters, you should consider the character set of the datafile. When the character set of the datafile is different than that of the control file, you can specify the delimiters in hexadecimal (that is, X’<hexadecimal string>’). If the delimiters are specified in hexadecimal notation, the specification must consist of characters that are valid in the character set of the input datafile. In contrast, if hexadecimal specification is not used, the delimiter specification is considered to be in the client’s (that is, the control file’s) character set. In this case, the delimiter is converted into the datafile's character set before SQL*Loader searches for the delimiter in the datafile.
Note the following:
SQL strings are not supported for LOBs, BFILEs, object columns, nested tables, or VARRAYS; therefore, you cannot specify SQL strings as part of a FILLER field specification.
To facilitate loading, you have available a new keyword, FILLER. You use this keyword to specify a FILLER field, which is a datafile mapped field that does not correspond to a database column.
The FILLER field is assigned values from the datafield to which it is mapped. The FILLER field can be used as an argument to a number of functions, for example, NULLIF.
The syntax for a FILLER field is same as that for a column-based field, except that a FILLER field's name is followed by the keyword FILLER.
Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings.
Filler field specifications cannot contain a NULLIF/DEFAULTIF clause.
Filler fields are initialized to NULL if the TRAILING NULLCOLS is specified and applicable. If another field references a nullified FILLER field, an error is generated.
SQL*Loader DDL Support for LOBFILES and Secondary Data Files (SDFs)
The data to be loaded into some of the new datatypes, like LOBs and collections, can potentially be very lengthy. Consequently, it is likely that you will want to have such data instances out of line from the rest of the data. LOBFILES and secondary data files (SDFs) provide a method to separate lengthy data.
LOBFILES are relatively simple datafiles that facilitate LOB loading. The attribute that distinguishes LOBFILEs from the primary datafiles is that in LOBFILEs there is no concept of a record. In LOBFILEs the data is in any of the following type fields:
Note: The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE.
Note: A field read from a LOBFILE cannot be used as an argument to a clause (for example, the NULLIF clause).
Secondary data files (SDFs) are similar in concept to primary datafiles. Like primary datafiles, SDFs are a collection of records, and each record is made up of fields. The SDFs are specified on a per control-file-field basis.
You use the SDF keyword to specify SDFs. The SDF keyword can be followed by either the file specification string, or a FILLER field that is mapped to a datafield containing one or more file specification strings.
As for a primary datafile, the following can be specified for each SDF:
Full Field Names
Be aware that with SQL*Loader support for complex datatypes like column-objects, the possibility arises that two identical field names could exist in the control file, one corresponding to a column, the other corresponding to a column object’s attribute. Certain clauses can refer to fields (for example, WHEN, NULLIF, DEFAULTIF, SID, OID, REF, BFILE, and so on), causing a naming conflict if identically named fields exist in the control file.
Therefore, if you use clauses that refer to fields, you must specify the full name. For example, if field fld1 is specified to be a COLUMN OBJECT and it contains field fld2, when specifying fld2 in a clause such as NULLIF, you must use the full field name fld1.fld2.
When to Use LOBFILEs or SDFs
An example situation in which you might use LOBFILES or SDFs would be if you needed to load employee names, employee IDs, and employee resumes. You could read the employee names and IDs from the main datafiles and you could read the resumes, which can be quite lengthy, from LOBFILEs.
Dynamic Versus Static LOBFILE and SDF Specifications
You can specify SDFs and LOBFILEs either statically (you specify the actual name of the file) or dynamically (you use a FILLER field as the source of the filename). In either case, when the EOF of an SDF or LOBFILE is reached, the file is closed and further attempts at sourcing data from that particular file produce results equivalent to sourcing data from an empty field.
In the case of the dynamic secondary file specification, this behavior is slightly different. Whenever the specification changes to reference a new file, the old file is closed and the data is read from the beginning of the newly referenced file.
The dynamic switching of the datasource files has a resetting effect. For example, when switching from the current file to a previously opened file, the previously opened file is reopened, and the data is read from the beginning of the file.
You should not specify the same SDF or LOBFILE as the source of two different fields. If you do so, typically, the two fields will read the data independently.
Oracle 8i Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 8i Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 8i Tutorial
Sql*loader Case Studies
Sql*loader Control File Reference
Sql*loader Command-line Reference
Sql*loader: Log File Reference
Sql*loader: Conventional And Direct Path Loads
Dbverify: Offline Database Verification Utility
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.