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

Details

Example

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;