SAS/ACCESS Interface to Oracle

Bulk Loading for Oracle

SAS/ACCESS Interface to Oracle can call the Oracle SQL*Loader (SQLLDR) when you set the data set option BULKLOAD=YES. The Oracle bulk loader provides superior load performance, so you can rapidly move data from a SAS file into an Oracle table. Future releases of SAS/ACCESS software will continue to use powerful Oracle tools to improve load performance. An Oracle bulk-load example is available.

Here are the Oracle bulk-load data set options. For detailed information about these options, see Data Set Options for Relational Databases.

BL_BADFILE=
BL_CONTROL=
BL_DATAFILE=
BL_DELETE_DATAFILE=
BL_DIRECT_PATH=
BL_DISCARDFILE=
BL_INDEX_OPTIONS=
BL_LOAD_METHOD=
BL_LOG=
BL_OPTIONS=
BL_PARFILE=
BL_PRESERVE_BLANKS=
BL_RECOVERABLE=
BL_RETURN_WARNINGS_AS_ERRORS=
BL_SQLLDR_PATH=
BL_SUPPRESS_NULLIF=
BULKLOAD=

BULKLOAD= calls the Oracle bulk loader so that the Oracle engine can move data from a SAS file into an Oracle table using SQL*Loader (SQLLDR).

Note: SQL*Loader direct-path load has a number of limitations. See your Oracle utilities documentation for details, including tips to boost performance. You can also view the SQL*Loader log file instead of the SAS log for information about the load when you use bulk load. [cautionend]

Interactions with Other Options

When BULKLOAD=YES, the following statements are true:

The DBCOMMIT=, DBFORCE=, ERRLIMIT=, and INSERTBUFF= options are ignored.
If NULLCHAR=SAS, and the NULLCHARVAL value is blank, then the SQL*Loader attempts to insert a NULL instead of a NULLCHARVAL value.
If NULLCHAR=NO, and the NULLCHARVAL value is blank, then the SQL*Loader attempts to insert a NULL even if the DBMS does not allow NULL.

To avoid this result, set BL_PRESERVE_BLANKS=YES or set NULLCHARVAL to a non-blank value and then replace the non-blank value with blanks after processing, if necessary.

z/OS Specifics

When you use bulk load in the z/OS operating environment, the files that the SQL*Loader uses must conform to z/OS data set standards. The data sets can be either sequential data sets or partitioned data sets. Each filename that is supplied to the SQL*Loader are subject to extension and FNA processing.

If you do not specify filenames using data set options, then default names in the form of userid.SAS.data-set-extension apply. The userid is the TSO prefix when running under TSO, and it is the PROFILE PREFIX in batch. The data-set-extensions are:

	BAD for the bad file
	CTL for the control file
	DAT for the data file
	DSC for the discard file
	LOG for the log file

If you want to specify filenames using data set options, then you must use one of these forms:

	/DD/ddname
	/DD/ddname(membername)
	Name

For detailed information about these forms, see the SQL*Loader chapter in the Oracle user's guide for z/OS.

The Oracle engine runs the SQL*Loader by issuing a host-system command from within your SAS session. The data set where the SQLLDR executable file resides must be available to your TSO session or allocated to your batch job. Check with your system administrator if you do not know the name or availability of the data set that contains the SQLLDR executable file.

On z/OS, the bad file and the discard file are, by default, not created in the same format as the data file. This makes it difficult to load the contents of these files after making corrections. See the section on SQL*Loader file attributes in the SQL*Loader section in the Oracle user's guide for z/OS for information about overcoming this limitation.

Example

This example shows you how to create and use a SAS data set to create and load to a large Oracle table, FLIGHTS98. This load uses the SQL*Loader direct path method because you specified BULKLOAD=YES. BL_OPTIONS= passes the specified SQL*Loader options to SQL*Loader when it is invoked. In this example, you can use the ERRORS= option to have up to 899 errors in the load before it terminates and the LOAD= option loads the first 5,000 rows of the input data set, SASFLT.FLT98.

options yearcutoff=1925;   /* included for Year 2000 compliance */

libname sasflt 'SAS-Data-Library';
libname ora_air oracle user=testuser password=testpass 
   path='ora8_flt' schema=statsdiv; 

data sasflt.flt98;
   input flight $3. +5 dates date7. +3 depart time5. +2 orig $3.
         +3 dest $3.  +7 miles +6 boarded +6 capacity;
   format dates date9. depart time5.;
   informat dates date7. depart time5.;
   datalines;
114     01JAN98    7:10  LGA   LAX       2475       172       210
202     01JAN98   10:43  LGA   ORD        740       151       210
219     01JAN98    9:31  LGA   LON       3442       198       250

<...10,000 more observations...>

proc sql; 
create table ora_air.flights98
(BULKLOAD=YES BL_OPTIONS='ERRORS=899,LOAD=5000') as
   select * from sasflt.flt98;
quit;

During a load, certain SQL*Loader files are created, such as the data, log, and control files. Unless otherwise specified, they are given a default name and written to the current directory. For this example, the default names would be bl_flights98.dat , bl_flights98.log , and bl_flights98.ctl .

Top of Page