Tài liệu ORACLE8i- P26 pptx

999

employees COLUMN OBJECT

(emp_name) CHAR(30),

emp_age INTEGER EXTERNAL(3),

emp_id CHAR(7) NULLIF emps.emps.emp_id=BLANKS)),

projects NESTED TABLE SDF(CONSTANT ’pr.txt’ “fix 71”)

(project_id char(5) INTEGER EXTERNAL(5),

project_name char(30) CHAR

NULLIF projects.project_name=BLANKS,

proj_desc LOBFILE( proj_desc.txt) CHAR(2000)

TERMINATED BY “<>\n”))

In this example we have a VARRAY of employees and a nested table of projects. If

the filler column EMP_CNT is 0, then the array is initialized to EMPTY; and if the

COUNT is greater then 0, then the VARRAY is created with the number of elements

equal to EMP_CNT. As for the nested table of projects, we just load the data into the

nested table, since it has no limitations on rows as does the VARRAY.

LOBs

Following are the four types of LOBs (Large Objects) supported by SQL*Loader pro￾cessing:

• BLOB is a Binary Large Object that can store up to 4GB of data. This type actu￾ally stores its data in the table.

• CLOB is a Character Large Object that can store up to 4GB of data. This type

actually stores its data in the table.

• NCLOB is a national character set of CLOB. This type actually stores its data in

the table.

• BFILE means Binary Large Object. This type is only a pointer to an external file

supported and maintained by the operating system. Unlike the other three LOB

types, the BFILE has no size limit imposed by Oracle but is limited by the oper￾ating system.

Listing 22.14 has two examples of loading LOBs in SQL*Loader. In the first, the

LOBs are loaded from a delimited field; the second example loads LOBs from LOB files.

NOTE During the load process, SQL*Loader reads the LOB file in 64KB chunks and stores

the data like any other record type. In order for Oracle to load physical records larger than

64KB, you must use the READSIZE command-line parameter to specify a larger record size.

LOADING OBJECTS, COLLECTIONS, AND LOBS

Beyond Simple

Database Managment

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

1000 CHAPTER 22 • SQL*LOADER

Listing 22.14: Loading LOBs

LOAD DATA

INFILE ‘lob_example.dat’ “str ‘|’”

INTO TABLE candidate

FIELDS TERMINATED BY ‘,’

(name CHAR(25),

“RESUME” CHAR(507) ENCLOSED BY ‘<beg_lob>’ AND ‘<end_lob>’)

lob_example.dat

Mark Blomberg,<startlob> Mark D. Blomberg

1234 East Park Vista

[email protected]

Resume info here <endlob>

|Robert Freeman,<startlob> Robert G. Freeman

1234 West Bay Drive

[email protected]

Resume info here <endlob>

LOAD DATA

INFILE ‘lob2_example.dat’

INTO TABLE candidate

FIELDS TERMINATED BY ‘,’

(name CHAR(20),

ext_fname FILLER CHAR(40),

“RESUME” LOBFILE(ext_fname) TERMINATED BY EOF)

Mark Blomberg,mdb_resume.txt,

Robert Freeman,’/candidate/rgf_resume.txt’,

Mdb_rsume.txt

Mark D. Blomberg

1234 East Park Vista

[email protected]

Resume info here

Rgf_resume.txt

Robert G. Freeman

1234 West Bay Drive

[email protected]

Resume info here

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

1001

In the first example, the control file specifies that the separator between records is

the | character, and that the LOB has the identifying markers of <beg_lob> and

<end_lob>. This object could be used to store a person’s name and resume, facilitating

quick online access of a person’s employment qualifications. You could add other

columns to the table to hold other relevant information.

Loading Partitioned Objects

As fully discussed in Chapter 24, in Oracle a partitioned table is one that is separated

(grouped) into logical sets. SQL*Loader supports loading of partitioned tables with all

load types (conventional path, direct path, and parallel direct path). SQL*Loader has

made this transparent and you will not need to do any extra control file setup or data

processing to handle this type of table. All the topics covered in this chapter apply to

partitioned tables, as well.

Loading of the following partitioned objects is supported in Oracle 8i SQL*Loader:

• A single partition of a partitioned table

• Multiple partitions of a partitioned table

• All partitions of a partitioned table

• Nonpartitioned table

Running SQL*Loader

You can run SQL*Loader from the command line, from a script, or using a parameter

file. In this section, all examples demonstrate execution of SQL*Loader in a Unix

environment.

SQL*Loader from the Command Line

Command-line execution of SQL*Loader is easy. From the system prompt, you enter

sqlldr with the required series of keywords and values. (Refer to your Oracle docu￾mentation for the specific command to execute on your operating system.) If you

issue sqlldr with no keywords, SQL*Loader displays all the available keywords and

default values. Listing 22.15 shows the keywords and default values that are the same

across all operating systems.

RUNNING SQL*LOADER

Beyond Simple

Database Managment

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

1002 CHAPTER 22 • SQL*LOADER

Listing 22.15: SQL*Loader Command Options (All Platforms)

Sqlldr

userid -- Oracle username/password

control -- Control file name

log -- Log file name

bad -- Bad file name

data -- Data file name

discard -- Discard file name

discardmax -- Number of discards to allow

(Default all)

skip -- Number of logical records to skip

(Default 0)

load -- Number of logical records to load

(Default all)

errors -- Number of errors to allow

(Default 50)

rows -- Number of rows in conventional path bind array

or between direct path data saves

(Default: Conventional Path 64, Direct path all)

bindsize -- Size of conventional path bind array in bytes

(System-dependent default)

silent -- Suppress messages during run

(header, feedback, errors, discards, partitions, all)

direct -- Use direct path

(Default FALSE)

parfile -- Parameter file: name of file that contains

parameter specifications

parallel -- Perform parallel load

(Default FALSE)

readsize -- Size (in bytes) of the read buffer

file -- File to allocate extents from

Here is an example of starting SQL*Loader from the command line:

sqlldr userid=mark/mark_sql control=1461c1.ctl log=servtype.log

data=servtype.csv bad=servtype.bad discard=servtyp.dis

parallel=true errors=10000 rows=1000 bindsize=100000000

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com