Creates a file that contains the SQL*Loader command-line options.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Default: | none |
| Requirement: | To specify this option, you must first specify BULKLOAD=YES. |
| Data source: | Oracle |
| Tip: | The parse file is deleted at the end of SQL*Loader processing. |
| See: | BULKLOAD= data set option |
Table of Contents
the name that you give the file that contains the SQL*Loader command line options. It can also specify the path. If you do not specify a path, the file is created in the temporary file directory that is specified by the UTILLOC= system option.
This option prompts the SQL*Loader to use the PARFILE= option. This SQL*Loader option lets you specify SQL*Loader command-line options in a file instead of as command-line options.
Starting in SAS 9.4M9, this option is used automatically when you set BULKLOAD=YES. If you do not specify BL_PARFILE=, a parameter file is generated automatically in the location that is used for temporary files. Parameter values are populated from the bulk-load options that you specify in a DATA step.
Here is an example of how you can call the SQL*Loader by specifying user ID and control options.
sqlldr userid=myusr1/mypwd1 control=example.ctlYou can also call it by using the PARFILE= option.
sqlldr parfile=example.parExample.par now contains the USERID= and CONTROL= options. Security is a major advantage of using the BL_PARFILE= option because the user ID and password are stored in a separate file.
Permissions on the file default to operating system defaults. Create the file in a protected directory to prevent unauthorized users from accessing its contents.
To display the contents
of the parse file in the SAS log, use the SASTRACE=",,,d" option.
The password is blocked out and replaced with xxxx,
however.
This example demonstrates how SQL*Loader invocation differs when you specify the BL_PARFILE= option.
libname x oracle user=myusr1 pw=mypwd1;
/* In this DATA step, call the SQL*Loader without BL_PARFILE=.
sqlldr userid=myusr1/mypwd1@oraclev9
control=bl_bltst_0.ctl log=bl_bltst_0.log
bad=bl_bltst_0.bad discard=bl_bltst_0.dsc
*/
data x.bltst ( bulkload=yes);
c1=1;
run;
/* In this DATA step, call the SQL*Loader with BL_PARFILE=.
sqlldr parfile=test.par
In this case all options are written to the test.par file.
*/
data x.bltst2 ( bulkload=yes bl_parfile='test.par');
c1=1;
run;