Bulk load problem in SAS Data Intergration

gyambqt — Wed, 07 Jun 2017 06:48:29 GMT

Hello expert,

I have two SAS transform, one is bulk load and one is table load

I have a SAS source table with column1 that contain blank value.

If I load the table into Oracle then the blank value will be treated as null value in Oracle.

However I want to keep it as blank character rather than null value in oracle.

so I set column1 in Oracle ="cannot be null"

after I have done I run the table loader and bulk load transform in SAS Data Intergration

table loader is working perfectly(blank value from source table has been treated as blank in oracle) but bulk load failed (all the blank values from source table has failed to load into oracle).

I opened both table loader and bulk load code they are below:

column1 is varchar2 in oracle

table loader code:

/*Oracle is the Oracle lib */
   data Orcacle.table
           (dbnull = (
                      CODE_CD = NO
            ));
      attrib CODE_CD length = $10
         format = $10.
         informat = $10.
         label = 'CODE_CD';
run;

   proc append base = oracle.table
      data = &etls_lastTable (&etls_tableOptions) force ;
    run;

bulk load code:

Oracle is Oracle lib
   data Orcacle.table
           (dbnull = (
                      CODE_CD = NO
            ));
      attrib CODE_CD length = $10
         format = $10.
         informat = $10.
         label = 'CODE_CD';
run;

proc append base = oracle.table (&G_BULKLOAD_DELETE_FILES_STMT.
   &G_BULKLOAD_DIRECT_PATH_STMT. BULKLOAD = YES
   BL_LOAD_METHOD = replace )
      data = &etls_lastTable (&etls_tableOptions) force ;
    run;

the warning message I was getting from bulk load is

WARNING: All or some rows were rejected/discarded!(or possibly there were other errors like Index maintenance errors) ********************************************************************** Please look in SQL*Loader log for the load results. SQL*Loader Log location(only if BL_DELETE_FILES=NO is set): -- /sasdata/data/project/work/DBMS/BL_TR_CO

I realised table loader can handle to problem and want to know why bulk load cannot handle the problem and how to use bulk load to handle the problem.

Thanks

Re: Bulk load problem in SAS Data Intergration

LinusH — Wed, 07 Jun 2017 12:09:46 GMT

BL_PRESERVE_BLANKS=YES?

I have no idea what was going on in your other thread, so...

Re: Bulk load problem in SAS Data Intergration

gyambqt — Wed, 07 Jun 2017 23:16:23 GMT

I realised BL_PRESERVE_BLANKS=YES will create the trailing blank for all the columns which is not the desired outcome we are seeking for.

By setting DBnull cannot be null for the column in the target oracle table, the table load option allows inserting a blank value into Oracle table. But it didn't work for the bulk load transform and want to find out reason behind that

topic Bulk load problem in SAS Data Intergration in SAS Data Management

Bulk load problem in SAS Data Intergration

Re: Bulk load problem in SAS Data Intergration

BL_PRESERVE_BLANKS=YES?

Re: Bulk load problem in SAS Data Intergration