BL_PARFILE= Data Set Option

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)
Default: none
Requirement: To specify this option, you must first set BULKLOAD=YES.
Data source: Oracle
Tip: The parse file is deleted at the end of SQL*Loader processing.
See: BULKLOAD= data set option

Syntax

BL_PARFILE=<parse-file>

Syntax Description

parse-file
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 current directory.

Details

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. Here is an example of how you can call the SQL*Loader by specifying user ID and control options.
sqlldr userid=scott/tiger control=example.ctl
You can also call it by using the PARFILE = option.
sqlldr parfile=example.par
Example.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.

Example: Invoke SQL*Loader Using BL_PARFILE=

This example demonstrates how SQL*Loader invocation is different when you specify the BL_PARFILE= option.
libname x oracle user=scott pw=tiger;
  /* SQL*Loader is invoked as follows without BL_PARFILE= */
   sqlldr userid=scott/tiger@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;
   /* Note how SQL*Loader is invoked in this 
      DATA step, which uses 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;