|Oracle9i Database Utilities
Release 1 (9.0.1)
Part Number A90192-01
This chapter discusses the following topics:
Column objects in the control file are described in terms of their attributes. If the object type on which the column object is based is declared to be nonfinal, then the column object in the control file may be described in terms of the attributes, both derived and declared, of any subtype derived from the base object type. In the datafile, the data corresponding to each of the attributes of a column object is in a data field similar to that corresponding to a simple relational column.
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,
Therefore, if you use clauses that refer to fields, you must specify the full name. For example, if field
The following sections show examples of loading column objects:
Example 7-1 shows a case in which the data is in predetermined size fields. The newline character marks the end of a physical record. You can also mark the end of a physical record by using a custom record separator in the operating system file-processing clause (
LOAD DATA INFILE 'sample.dat' INTO TABLE departments (dept_no POSITION(01:03) CHAR, dept_name POSITION(05:15) CHAR, 1 dept_mgr COLUMN OBJECT (name POSITION(17:33) CHAR, age POSITION(35:37) INTEGER EXTERNAL, emp_id POSITION(40:46) INTEGER EXTERNAL) )
Example 7-2 shows a case in which the data is in delimited fields.
LOAD DATA 1 INFILE 'sample.dat' "var 6" INTO TABLE departments FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 2 (dept_no dept_name, dept_mgr COLUMN OBJECT (name CHAR(30), age INTEGER EXTERNAL(5), emp_id INTEGER EXTERNAL(5)) )
"var"string includes the number of bytes in the length field at the beginning of each record (in this example, the number is 6). If no value is specified, the default is 5 bytes. The maximum size of a variable record is 2^32-1. Specifying larger values will result in an error.
CHARof length 255.
Example 7-3 shows a control file describing nested column objects (one column object nested in another column object).
LOAD DATA INFILE `sample.dat' INTO TABLE departments_v2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (dept_no CHAR(5), dept_name CHAR(30), dept_mgr COLUMN OBJECT (name CHAR(30), age INTEGER EXTERNAL(3), emp_id INTEGER EXTERNAL(7), 1 em_contact COLUMN OBJECT (name CHAR(30), phone_num CHAR(20))))
101,Mathematics,Johny Q.,30,1024,"Barbie",650-251-0010, 237,Physics,"Albert Einstein",65,0000,Wife Einstein,654-3210,
Example 7-4 shows a case in which a nonfinal base object type has been extended to create a new derived subtype. Although the column object in the table definition is declared to be of the base object type, SQL*Loader allows any subtype to be loaded into the column object, provided that the subtype is derived from the base object type.
CREATE TYPE person_type AS OBJECT (name VARCHAR(30), ssn NUMBER(9)) not final; CREATE TYPE employee_type UNDER person_type (empid NUMBER(5)); CREATE TABLE personnel (deptno NUMBER(3), deptname VARCHAR(30), person person_type);
LOAD DATA INFILE 'sample.dat' INTO TABLE personnel FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (deptno INTEGER EXTERNAL(3), deptname CHAR, 1 person COLUMN OBJECT TREAT AS employee_type (name CHAR, ssn INTEGER EXTERNAL(9), 2 empid INTEGER EXTERNAL(5)))
ASclause indicates that SQL*Loader should treat the column object
personas if it were declared to be of the derived type
employee_type, instead of its actual declared type,
empidattribute is allowed here because it is an attribute of the
employee_type. If the
ASclause had not been specified, this attribute would have resulted in an error, because it is not an attribute of the column's declared type.
Specifying null values for nonscalar datatypes is somewhat more complex than for scalar datatypes. An object can have a subset of its attributes be null, it can have all of its attributes be null (an attributively null object), or it can be null itself (an atomically null object).
In fields corresponding to column objects, you can use the
NULLIF clause to specify the field conditions under which a particular attribute should be initialized to
NULL. Example 7-5 demonstrates this.
LOAD DATA INFILE 'sample.dat' INTO TABLE departments (dept_no POSITION(01:03) CHAR, dept_name POSITION(05:15) CHAR NULLIF dept_name=BLANKS, dept_mgr COLUMN OBJECT 1 ( name POSITION(17:33) CHAR NULLIF dept_mgr.name=BLANKS, 1 age POSITION(35:37) INTEGER EXTERNAL NULLIF dept_mgr.age=BLANKS, 1 emp_id POSITION(40:46) INTEGER EXTERNAL NULLIF dept_mgr.emp_id=BLANKS))
NULLIFclause corresponding to each attribute states the condition under which the attribute value should be
dept_mgrvalue is null. The
dept_namevalue is also null.
To specify in the control file the condition under which a particular object should take null value (atomic null), you must follow that object's name with a
NULLIF clause based on a logical combination of any of the mapped fields (for example, in Example 7-5, the named mapped fields would be
dept_mgr would not be a named mapped field because it does not correspond (is not mapped) to any field in the datafile).
Although the preceding is workable, it is not ideal when the condition under which an object should take the value of null is independent of any of the mapped fields. In such situations, you can use filler fields.
You can map a filler field to the field in the datafile (indicating if a particular object is atomically null or not) and use the filler field in the field condition of the
NULLIF clause of the particular object. This is shown in Example 7-6.
LOAD DATA INFILE 'sample.dat' INTO TABLE departments_v2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (dept_no CHAR(5), dept_name CHAR(30), 1 is_null FILLER CHAR, 2 dept_mgr COLUMN OBJECT NULLIF is_null=BLANKS (name CHAR(30) NULLIF dept_mgr.name=BLANKS, age INTEGER EXTERNAL(3) NULLIF dept_mgr.age=BLANKS, emp_id INTEGER EXTERNAL(7) NULLIF dept_mgr.emp_id=BLANKS, em_contact COLUMN OBJECT NULLIF is_null2=BLANKS (name CHAR(30) NULLIF dept_mgr.em_contact.name=BLANKS, phone_num CHAR(20) NULLIF dept_mgr.em_contact.phone_num=BLANKS)), 1 is_null2 FILLER CHAR)
101,Mathematics,n,Johny Q.,,1024,"Barbie",608-251-0010,, 237,Physics,,"Albert Einstein",65,0000,,650-654-3210,n,
CHAR(because it is a delimited field, the
CHAR(255)). Note that the
NULLIFclause is not applicable to the filler field itself.
is_nullfield is blank.
The control file syntax required to load an object table is nearly identical to that used to load a typical relational table. Example 7-7 demonstrates loading an object table with primary key object identifiers (OIDs).
LOAD DATA INFILE 'sample.dat' DISCARDFILE 'sample.dsc' BADFILE 'sample.bad' REPLACE INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (name CHAR(30) NULLIF name=BLANKS, age INTEGER EXTERNAL(3) NULLIF age=BLANKS, emp_id INTEGER EXTERNAL(5))
By looking only at the preceding control file you might not be able to determine if the table being loaded was an object table with system-generated OIDs (real OIDs), an object table with primary key OIDs, or a relational table.
You may want to load data that already contains real OIDs and to specify that instead of generating new OIDs, the existing OIDs in the datafile should be used. To do this, you would follow the
TABLE clause with the OID clause:
In this clause,
fieldname is the name of one of the fields (typically a filler field) from the field specification list that is mapped to a data field that contains the real OIDs. SQL*Loader assumes that the OIDs provided are in the correct format and that they preserve OID global uniqueness. Therefore, to ensure uniqueness, you should use the Oracle OID generator to generate the OIDs to be loaded.
The OID clause can only be used for system-generated OIDs, not primary key OIDs.
Example 7-8 demonstrates loading real OIDs with the row-objects.
LOAD DATA INFILE 'sample.dat' INTO TABLE employees_v2 1 OID (s_oid) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (name CHAR(30) NULLIF name=BLANKS, age INTEGER EXTERNAL(3) NULLIF age=BLANKS, emp_id INTEGER EXTERNAL(5), 2 s_oid FILLER CHAR(32))
3 Johny Quest, 18, 007, 21E978406D3E41FCE03400400B403BC3, Speed Racer, 16, 000, 21E978406D4441FCE03400400B403BC3,
s_oidloader field contains the OID. The parentheses are required.
s_oiddoes not contain a valid hexadecimal number, the particular record is rejected.
RAWand stored in the object table.
If an object table's row object is based on a nonfinal type, SQL*Loader allows for any derived subtype to be loaded into the object table. As previously mentioned, the syntax required to load an object table with a derived subtype is almost identical to that used for a typical relational table. However, in this case, the actual subtype to be used must be named, so that SQL*Loader can determine if it is a valid subtype for the object table.
Consider the following object type and object table definitions:
CREATE TYPE employees_type AS OBJECT (name VARCHAR2(30), age NUMBER(3), emp_id NUMBER(5)) not final; CREATE TYPE hourly_emps_type UNDER employees_type (hours NUMBER(3)); CREATE TABLE employees_v3 of employees_type;
LOAD DATA INFILE 'sample.dat' INTO TABLE employees_v3 1 TREAT AS hourly_emps_type FIELDS TERMINATED BY ',' (name CHAR(30), age INTEGER EXTERNAL(3), emp_id INTEGER EXTERNAL(5), 2 hours INTEGER EXTERNAL(2))
ASclause indicates that SQL*Loader should treat the object table as if it were declared to be of type
hourly_emps_type, instead of its actual declared type,
hoursattribute is allowed here because it is an attribute of the
hourly_emps_type. If the
ASclause had not been specified, this attribute would have resulted in an error, because it is not an attribute of the object table's declared type.
SQL*Loader can load real
REF columns (
REFs containing real OIDs of the referenced objects) as well as primary key
SQL*Loader assumes, when loading real
REF columns, that the actual OIDs from which the
REF columns are to be constructed are in the datafile with the rest of the data. The description of the field corresponding to a
REF column consists of the column name followed by the
REF clause takes as arguments the table name and an OID. Note that the arguments can be specified either as constants or dynamically (using filler fields). See ref_spec for the appropriate syntax. Example 7-10 demonstrates real
LOAD DATA INFILE `sample.dat' INTO TABLE departments_alt_v2 FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"' (dept_no CHAR(5), dept_name CHAR(30), 1 dept_mgr REF(t_name, s_oid), s_oid FILLER CHAR(32), t_name FILLER CHAR(30))
22345, QuestWorld, 21E978406D3E41FCE03400400B403BC3, EMPLOYEES_V2, 23423, Geography, 21E978406D4441FCE03400400B403BC3, EMPLOYEES_V2,
dept_mgrfield itself does not map to any field in the datafile.
To load a primary key
REF column, the SQL*Loader control-file field description must provide the column name followed by a
REF clause. The
REF clause takes for arguments a comma-separated list of field names and constant values. The first argument is the table name, followed by arguments that specify the primary key OID on which the
REF column to be loaded is based. See ref_spec for the appropriate syntax.
SQL*Loader assumes that the ordering of the arguments matches the relative ordering of the columns making up the primary key OID in the referenced table. Example 7-11 demonstrates loading primary key
LOAD DATA INFILE `sample.dat' INTO TABLE departments_alt FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"' (dept_no CHAR(5), dept_name CHAR(30), dept_mgr REF(CONSTANT `EMPLOYEES', emp_id), emp_id FILLER CHAR(32))
A LOB is a large object type. SQL*Loader supports the following types of LOBs:
BLOB: an internal LOB containing unstructured binary data.
CLOB: an internal LOB containing character data.
NCLOB: an internal LOB containing characters from a national character set.
BLOBstored outside of the database tablespaces in a server-side operating system file.
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."
XML columns are columns declared to be of type
XMLTYPE. SQL*Loader treats
XML columns as if they were
CLOBs. All of the methods described in the following sections for loading LOB data from the primary datafile or from LOBFILEs are applicable to loading
Because LOBs can be quite large, SQL*Loader is able to load LOB data from either a primary datafile (in line with the rest of the data) or from LOBFILEs. This section addresses the following topics:
To load internal LOBs (
XML columns from a primary datafile, you can use the following standard SQL*Loader formats:
Each of these formats is described in the following sections.
This is a very fast and conceptually simple format in which to load LOBs, as shown in Example 7-12.
To load LOBs using this format, you should use either
RAW as the loading datatype.
LOAD DATA INFILE 'sample.dat' "fix 501" INTO TABLE person_table (name POSITION(01:21) CHAR, 1 "RESUME" POSITION(23:500) CHAR DEFAULTIF "RESUME"=BLANKS)
NULLIFclause were used instead of the
DEFAULTIFclause. You can use SQL*Loader datatypes other than
CHARto load LOBs. For example, when loading
BLOBs, you would probably want to use the
This format handles LOBs of different sizes within the same column (datafile field) without problem. However, this added flexibility can affect performance, because SQL*Loader must scan through the data, looking for the delimiter string.
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,
'). 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:
WHITESPACE, the leading whitespaces are trimmed.
Example 7-13 shows an example of loading LOB data in delimited fields.
LOAD DATA INFILE 'sample.dat' "str '|'" INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(25), 1 "RESUME" CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>')
Johny Quest,<startlob> Johny Quest 500 Oracle Parkway firstname.lastname@example.org ... <endlob> 2 |Speed Racer, .......
<endlob>are the enclosure strings. With the default byte-length semantics, the maximum length for a LOB that can be read using
CHAR(507)is 507 bytes. If character-length semantics were used, the maximum would be 507 characters. See Character-Length Semantics.
'|'had been placed right after
<endlob>and followed with the newline character, the newline would have been interpreted as part of the next record. An alternative would be to make the newline part of the record separator (for example,
'|\n'or, in hexadecimal notation,
You can use
VARRAW datatypes to load LOB data organized in length-value pair fields. This method of loading provides better performance than using delimited fields, but can reduce flexibility (for example, you must know the LOB length for each LOB before loading). Example 7-14 demonstrates loading LOB data in length-value pair fields.
LOAD DATA 1 INFILE 'sample.dat' "str '<endrec>\n'" INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(25), 2 "RESUME" VARCHARC(3,500))
Johny Quest,479 Johny Quest 500 Oracle Parkway email@example.com ... <endrec> 3 Speed Racer,000<endrec>
CLOBcolumn. In the control file, it is a
VARCHARC, whose length field is 3 bytes long and whose maximum size is 500 bytes (with byte-length semantics). If character-length semantics were used, the length would be 3 characters and the maximum size would be 500 characters. See Character-Length Semantics.
VARCHARCis 0 (the value subfield is empty). Consequently, the LOB instance is initialized to empty.
BFILE datatype stores unstructured binary data in operating system files outside the database. A
BFILE column or attribute stores a file locator that points to the external file containing the data. The file to be loaded as a
BFILE does not have to exist at the time of loading; it can be created later. SQL*Loader assumes that the necessary directory objects have already been created (a logical alias name for a physical directory on the server's file system). For more information, see the Oracle9i Application Developer's Guide - Large Objects (LOBs).
A control file field corresponding to a
BFILE column consists of column name followed by the
BFILE clause. The
BFILE clause takes as arguments a DIRECTORY OBJECT (the server_directory alias) name followed by a
BFILE name. Both arguments can be provided as string constants, or they can be dynamically loaded through some other field. See the Oracle9i SQL Reference for more information.
LOAD DATA INFILE sample.dat INTO TABLE planets FIELDS TERMINATED BY ',' (pl_id CHAR(3), pl_name CHAR(20), fname FILLER CHAR(30), 1 pl_pict BFILE(CONSTANT "scott_dir1", fname))
LOAD DATA INFILE sample.dat INTO TABLE planets FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (pl_id NUMBER(4), pl_name CHAR(20), fname FILLER CHAR(30), 1 dname FILLER CHAR(20), pl_pict BFILE(dname, fname) )
1, Mercury, mercury.jpeg, scott_dir1, 2, Venus, venus.jpeg, scott_dir1, 3, Earth, earth.jpeg, scott_dir2,
LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE instead of from a primary datafile. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.
There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64 KB chunks.
In LOBFILEs the data can be in any of the following types of fields:
BLANKS is not applicable to fields read from a LOBFILE.
VARCHARCloader datatypes--are used for loading from this type of field.
See Examples of Loading LOB Data from LOBFILEs for examples of using each of these field types. All of the previously mentioned field types can be used to load
See lobfile_spec for LOBFILE syntax.
You can specify 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 a LOBFILE is reached, the file is closed and further attempts to read data from that file produce results equivalent to reading data from an empty field.
You should not specify the same LOBFILE as the source of two different fields. If you do so, typically, the two fields will read the data independently.
This section contains examples of loading data from different types of fields in LOBFILEs.
In Example 7-17, each LOBFILE is the source of a single LOB. To load LOB data that is organized in this way, you follow the column or field name with the LOBFILE datatype specifications.
LOAD DATA INFILE 'sample.dat' INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(20), 1 ext_fname FILLER CHAR(40), 2 "RESUME" LOBFILE(ext_fname) TERMINATED BY EOF)
CHARdatatype. This assumes the use of default byte-length semantics. If character-length semantics were used, the field would be mapped to a 40-character data field.
ext_fnamefiller field. It then loads the data from the LOBFILE (using the
CHARdatatype) from the first byte to the EOF character. If no existing LOBFILE is specified, the
"RESUME"field is initialized to empty. See Loading a Parent Table Separately from Its Child Table.
In Example 7-18, you specify the size of the LOBs to be loaded into a particular column in the control file. During the load, SQL*Loader assumes that any LOB data loaded into that particular column is of the specified size. The predetermined size of the fields allows the data-parser to perform optimally. However, it is often difficult to guarantee that all LOBs are the same size.
LOAD DATA INFILE 'sample.dat' INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(20), 1 "RESUME" LOBFILE(CONSTANT '/usr/private/jquest/jqresume.txt') CHAR(2000))
jqresume.txtLOBFILE, using the
CHARdatatype, starting with the byte following the byte loaded last during the current loading session. This assumes the use of the default byte-length semantics. If character-length semantics were used, SQL*Loader would load 2000 characters of data, starting from the first character after the last-loaded character. See Character-Length Semantics.
In Example 7-19, the LOB data instances in the LOBFILE are delimited. In this format, loading different size LOBs into the same column is not a problem. However, this added flexibility can affect performance, because SQL*Loader must scan through the data, looking for the delimiter string.
LOAD DATA INFILE 'sample.dat' INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(20), 1 "RESUME" LOBFILE( CONSTANT 'jqresume') CHAR(2000) TERMINATED BY "<endlob>\n")
CHAR, SQL*Loader knows what to expect as the maximum length of the field, which can result in memory usage optimization. If you choose to specify a maximum length, you should be sure not to underestimate its value. The
BYclause specifies the string that terminates the LOBs. Alternatively, you could use the
BYclause allows a bit more flexibility as to the relative positioning of the LOBs in the LOBFILE (the LOBs in the LOBFILE need not be sequential).
In Example 7-20 each LOB in the LOBFILE is preceded by its length. You could use
VARRAW datatypes to load LOB data organized in this way.
This method of loading can provide better performance over delimited LOBs, but at the expense of some flexibility (for example, you must know the LOB length for each LOB before loading).
LOAD DATA INFILE 'sample.dat' INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(20), 1 "RESUME" LOBFILE(CONSTANT 'jqresume') VARCHARC(4,2000))
VARCHARC(4,2000)tells SQL*Loader that the LOBs in the LOBFILE are in length-value pair format and that the first 4 bytes should be interpreted as the length. The value of
2000tells SQL*Loader that the maximum size of the field is 2000 bytes. This assumes the use of the default byte-length semantics. If character-length semantics were used, the first 4 characters would be interpreted as the length in characters. The maximum size of the field would be 2000 characters. See Character-Length Semantics.
Questtells SQL*Loader that the LOB consists of the next 501 characters.
Keep in mind the following when you load data using LOBFILEs:
XMLcolumns can be loaded from LOBFILEs.
XMLcolumn, a null value will be inserted if there is a failure loading the LOB.
LOBtype column; nevertheless, if a maximum length is specified, SQL*Loader uses it as a hint to optimize memory usage. Therefore, it is important that the maximum length specification does not understate the true maximum length.
pos_spec) when loading data from a LOBFILE.
DEFAULTIFfield conditions cannot be based on fields read from LOBFILEs.
Like LOBs, collections can be loaded either from a primary datafile (data inline) or from secondary datafiles (data out of line). See Secondary Datafiles (SDFs) for details about SDFs.
When you load collection data, a mechanism must exist by which SQL*Loader can tell when the data belonging to a particular collection instance has ended. You can achieve this in two ways:
VARRAYinstance, use the DDL
COUNTfunction. The value specified for
COUNTmust either be a number or a character string containing a number, and it must be previously described in the control file before the
COUNTclause itself. This positional dependency is specific to the
cnt_fieldis 0 for the current row, results in a empty collection (not null), unless overridden by a
NULLIFclause. See count_spec.
BYclauses to specify a unique collection delimiter. This method cannot be used if an
SDFclause is used.
In the control file, collections are described similarly to column objects. See Loading Column Objects. There are some differences:
DEFAULTIFclause cannot refer to a field in an SDF unless the clause is on a field in the same SDF.
VARRAYof column objects, then the attributes of each column object will be in a nested field list.
The following restrictions exist for nested tables and VARRAYS:
field_listcannot contain a
col_obj_specnested within a varray cannot contain a
column_namespecified as part of the
field_listmust be the same as the
Example 7-21 demonstrates loading a
VARRAY and a nested table.
LOAD DATA INFILE `sample.dat' "str `\n' " INTO TABLE dept REPLACE FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"' ( dept_no CHAR(3), dname CHAR(25) NULLIF dname=BLANKS, 1 emps VARRAY TERMINATED BY ':' ( emps COLUMN OBJECT ( name CHAR(30), age INTEGER EXTERNAL(3), 2 emp_id CHAR(7) NULLIF emps.emps.emp_id=BLANKS ) ), 3 proj_cnt FILLER CHAR(3), 4 projects NESTED TABLE SDF (CONSTANT "pr.txt" "fix 57") COUNT (proj_ cnt) ( projects COLUMN OBJECT ( project_id POSITION (1:5) INTEGER EXTERNAL(5), project_name POSITION (7:30) CHAR NULLIF projects.projects.project_name = BLANKS ) ) )
BYclause specifies the
VARRAYinstance terminator (note that no
COUNTclause is used).
proj_cntis a filler field used as an argument to the
txtas the source of data. It also specifies a fixed-record format within the SDF.
COUNTis 0, then the collection is initialized to empty. Another way to initialize a collection to empty is to use a
DEFAULTIFclause. The main field name corresponding to the nested table field description is the same as the field name of its nested nonfiller-field, specifically, the name of the column object field description.
Secondary datafiles (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. They are useful when you load large nested tables and
SDFs are specified using the
SDF parameter. The
SDF parameter can be followed by either the file specification string, or a
FILLER field that is mapped to a data field containing one or more file specification strings.
As for a primary datafile, the following can be specified for each SDF:
CHARACTERSETclause (see Handling Different Character Encoding Schemes).
Also note the following with regard to SDFs:
READSIZEparameter to specify a larger physical record size. You can specify the
READSIZEparameter either from the command line or as part of an
You can specify SDFs 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 is reached, the file is closed and further attempts at reading data from that particular file produce results equivalent to reading data from an empty field.
In a 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 data source files has a resetting effect. For example, when SQL*Loader switches 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 as the source of two different fields. If you do so, typically, the two fields will read the data independently.
When you load a table that contains a nested table column, it may be possible to load the parent table separately from the child table. You can load the parent and child tables independently if the SIDs (system-generated or user-defined) are already known at the time of the load (that is, the SIDs are in the datafile with the data).
LOAD DATA INFILE `sample.dat' "str `|\n' " INTO TABLE dept FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"' TRAILING NULLCOLS ( dept_no CHAR(3), dname CHAR(20) NULLIF dname=BLANKS , mysid FILLER CHAR(32), 1 projects SID(mysid))
mysidis a filler field that is mapped to a datafile field containing the actual set-ids and is supplied as an argument to the
LOAD DATA INFILE `sample.dat' INTO TABLE dept FIELDS TERMINATED BY `,' OPTIONALLY ENCLOSED BY `"' TRAILING NULLCOLS 1 SID(sidsrc) (project_id INTEGER EXTERNAL(5), project_name CHAR(20) NULLIF project_name=BLANKS, sidsrc FILLER CHAR(32))
21034, "Topological Transforms", 21E978407D4441FCE03400400B403BC3, 77777, "Impossible Proof", 21E978408D4441FCE03400400B403BC3,
SIDclause tells SQL*Loader that it is loading the storage table for nested tables.
sidsrcis the filler field name that is the source of the real set-ids.
The following list describes some issues to keep in mind when you load
VARRAYs are created in the client's memory before they are loaded into the database. Each element of a
VARRAYrequires 4 bytes of client memory before it can be loaded into the database. Therefore, when you load a
VARRAYwith a thousand elements, you will require at least 4000 bytes of client memory for each
VARRAYinstance before you can load the
VARRAYs into the database. In many cases, SQL*Loader requires two to three times that amount of memory to successfully construct and load a
BINDSIZEparameter specifies the amount of memory allocated by SQL*Loader for loading records. Given the value specified for
BINDSIZE, SQL*Loader takes into consideration the size of each field being loaded, and determines the number of rows it can load in one transaction. The larger the number of rows, the fewer transactions, resulting in better performance.
But if the amount of memory on your system is limited, then at the expense of performance, you can specify a lower value for
ROWS than SQL*Loader calculated.
VARRAYs or a large number of smaller
VARRAYs could cause you to run out of memory during the load. If this happens, specify a smaller value for
ROWSand retry the load.