SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Bulk load problem in SAS Data Intergration

Reply
Regular Contributor
Posts: 152

Bulk load problem in SAS Data Intergration

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

Super User
Posts: 5,257

Re: Bulk load problem in SAS Data Intergration

BL_PRESERVE_BLANKS=YES?

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

Data never sleeps
Regular Contributor
Posts: 152

Re: Bulk load problem in SAS Data Intergration

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

Ask a Question
Discussion stats
  • 2 replies
  • 110 views
  • 0 likes
  • 2 in conversation