BULKLOAD=

BULKLOAD=

Loads rows of data as one unit.

Default value: NO

Alias: SQLLDR

Syntax
Example
	Creating a Table and Loading Data Using the BULKLOAD= and BL_OPTIONS= Options

Syntax

BULKLOAD=YES | NO

YES: Calls the ORACLE SQL*LOADER in order to insert or append rows to an ORACLE table.
NO: Uses the dynamic SAS/ACCESS LIBNAME engine to insert or append the data to an ORACLE table.

Details

Using BULKLOAD=YES is the fastest way to insert rows into a ORACLE table. If you specify NO and choose the transactional inserting of rows, you can improve performance by inserting multiple rows at a time. This performance enhancement is comparable to using the ORACLE SQL*LOADER Conventional Path Load. For more information about inserting multiple rows, see the SAS/ACCESS data set option, INSERTBUFF=.

Example

Example 1: Creating a Table and Loading Data Using the BULKLOAD= and BL_OPTIONS= Options

This example shows how to create a SAS data set and use it 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, the ERROR= option enables you to have 899 errors in the load before the load 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=louis password=fromage 
   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='ERROR=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.

Chapter Contents
Previous
Next
Top of Page