Data Set Options for Relational Databases

BL_PARFILE= Data Set Option

Creates a file that contains the SQL*Loader command line options.

Default value:	none
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
Example
See Also

Syntax

BL_PARFILE=<parse-file>

Syntax Description

parse-file: the name you give the file that contains the SQL*Loader command line options. The name can also specify the path. If no path is specified, the file is created in the current directory.

Details

To specify this option, you must first set BULKLOAD=YES.

This option prompts the SQL*Loader to use the PARFILE= option. This SQL*Loader option enables you to 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. One of the biggest advantages of using the BL_PARFILE= option is security because the user ID and password are stored in a separate file.

The permissions on the file default to the 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. However, the password is blocked out and replaced with xxxx .

Note: The parse file is deleted at the end of SQL*Loader processing. [cautionend]

Example

This example demonstrates how SQL*Loader invocation is different when the BL_PARFILE= option is specified.

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;