Bulk Loading for Oracle

Overview

SAS/ACCESS Interface to Oracle can call the Oracle SQL*Loader (SQLLDR) when you specify BULKLOAD=YES. The Oracle bulk loader provides superior load performance, so you can rapidly move data from a SAS file into an Oracle table.

Note: To preserve SQL*Loader special characters (= @ /) in the user name (USER=) in the LIBNAME statement, you must enclose all characters in double quotation marks and then enclose everything in single quotation marks (for example, '"scott@"').

Data Set Options with Bulk Loading

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

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.

Note: You can also view the SQL*Loader log file instead of the SAS log for information about the load when you use bulk loading.

Interactions with Other Options

When BULKLOAD=YES, these statements are true:

The DBCOMMIT=, DBFORCE=, ERRLIMIT=, and INSERTBUFF= options are ignored.
If NULLCHAR=SAS, and the NULLCHARVAL= value is blank, the SQL*Loader attempts to insert a NULL instead of a NULLCHARVAL value.
If NULLCHAR=NO, and the NULLCHARVAL value is blank, 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 loading 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 file name that is supplied to the SQL*Loader are subject to extension and FNA processing.

If you do not specify file names using data set options, 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 file names using data set options, 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.

By default, on z/OS the bad file and discard file are 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.

Examples

This example shows how you can use a SAS data set, SASFLT.FLT98, to create and load an Oracle table, FLIGHTS98.

libname sasflt 'SAS-data-library';
libname mydblib oracle server=mysrv1_users
   db=users user=myusr1 password=mypwd1 path=mysrv1;

proc sql;
create table net_air.flights98
   (BULKLOAD=YES
    BL_DATAFILE='c:\temp\oracle\data.dat'
    BL_USE_PIPE=NO
    BL_DELETE_DATAFILE=yes
    as select * from sasflt.flt98;
quit;

This next example shows how you can append the SAS data set, SASFLT.FLT98, to the existing Oracle table ALLFLIGHTS. The BL_USE_PIPE=NO option forces SAS/ACCESS Interface to Oracle to write data to a flat file, as specified in the BL_DATAFILE= option. Rather than deleting the data file, BL_DELETE_DATAFILE=NO causes the engine to leave it after the load has completed.

proc append base=new_air.flights98
    (BULKLOAD=YES
     BL_DATAFILE='c:\temp\oracle\data.dat'
     BL_USE_PIPE=NO
     BL_DELETE_DATAFILE=no
    data=sasflt.flt98;
run;

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. Also, the LOAD= option loads the first 5,000 rows of the input data set, SASFLT.FLT98.

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

libname sasflt 'SAS-library';
libname ora_air oracle user=myusr1 password=mypwd1
   path='mysrv1_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 temporary file directory that the UTILLOC= system option specifies. (For details about this option, see SAS System Options: Reference.) For this example, the default names are bl_flights98.dat, bl_flights98.log, and bl_flights98.ctl.

Last updated: February 3, 2026