Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

KenMac · Posted 06-15-2018 01:32 AM

Hi,

I found an issue where valid SAS datetime values are set to NULL when using the BL_API_BULKLOAD=YES option.

Created a sample program to illustrate the issue. There are three tests in the program:

Use a data step to load a date field - result is good
Use Oracle bulk load with the "BL_DIRECT_PATH=YES" option - result is good
Use Oracle bulk load with the "BL_API_BULKLOAD=YES" option - dates show as NULL in Oracle

Code and logs follow and screenshot of the final Oracle table is in attached PDF.

Environment: SAS 9.4M4 on Solaris SPARC / SAS Access for Oracle / Oracle 12c

Any suggestions? Thanks in advance!!

CODE


/*  What happened to my dates??  */

proc sql;
  drop table fram.oracle_date_data;
  quit;
run;

/*  Create sample SAS date field  */

data test_date;
  length load_type $ 20;
  retain sas_date_field  %sysfunc( datetime() );
  format sas_date_field  datetime20.;

  load_type = "Data Step          ";  output;
  load_type = "BL_DIRECT_PATH=YES ";  output;
  load_type = "BL_API_BULKLOAD=YES";  output;
 run;

/*  Copy to Oracle using SAS data step  */
/*  to define the base table            */

data fram.oracle_date_data;
  set test_date( where = ( load_type = "Data Step" ) );
run;

/*  Bulkload to Oracle using BL_DIRECT_PATH=YES  */

%let bl_options1 = %str( BL_SQLLDR_PATH="/apps/oracle/product/12.1.0.2/client/bin/sqlldr"
                         BL_RETURN_WARNINGS_AS_ERRORS=YES 
                         BL_DELETE_FILES=YES
                         BL_LOAD_METHOD=APPEND 
                         BULKLOAD=YES 
                         BL_DEFAULT_DIR="/data/saswork/tmp/"
                         BL_DIRECT_PATH=YES );

proc append base = fram.oracle_date_data( &bl_options1 )
            data = test_date( where = ( load_type = "BL_DIRECT_PATH=YES" ) );
run;

/*  Bulkload to Oracle using BL_DIRECT_PATH=YES and BL_API_BULKLOAD=YES  */
    
%let bl_options2 = %str( BULKLOAD=YES
                         BL_LOAD_METHOD=APPEND
                         BL_API_BULKLOAD=YES
                         BL_DIRECT_PATH=YES
                         INSERTBUFF = 20000     ); 

proc append base = fram.oracle_date_data( &bl_options2 )
            data = test_date( where = ( load_type = "BL_API_BULKLOAD=YES" ) );
run;

LOG

38         proc sql;
39           drop table fram.oracle_date_data;
  1289 1529039481 orprep 0 SQL (2) 
ORACLE_311: Prepared: on connection 3 1290 1529039481 orprep 0 SQL (2) 
SELECT * FROM fram.ORACLE_DATE_DATA 1291 1529039481 orprep 0 SQL (2) 
  1292 1529039481 orprep 0 SQL (2) 
WARNING: File FRAM.ORACLE_DATE_DATA.DATA does not exist.
WARNING: Table FRAM.ORACLE_DATE_DATA has not been dropped.
40           quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

41         run;
42         
43         /*  Create sample SAS date field  */
44         
45         data test_date;
46           length load_type $ 20;
47           retain sas_date_field  %sysfunc( datetime() );
48           format sas_date_field  datetime20.;
49         
50           load_type = "Data Step          ";  output;
51           load_type = "BL_DIRECT_PATH=YES ";  output;
52           load_type = "BL_API_BULKLOAD=YES";  output;
53          run;

NOTE: The data set WORK.TEST_DATE has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

54         
55         /*  Copy to Oracle using SAS data step  */
56         /*  to define the base table            */
57         
58         data fram.oracle_date_data;
59           set test_date( where = ( load_type = "Data Step" ) );
60         run;

  1293 1529039481 orprep 0 DATASTEP
ORACLE_312: Prepared: on connection 3 1294 1529039481 orprep 0 DATASTEP
SELECT * FROM fram.ORACLE_DATE_DATA 1295 1529039481 orprep 0 DATASTEP
  1296 1529039481 orprep 0 DATASTEP
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
  1297 1529039481 orexec 0 DATASTEP
ORACLE_313: Executed: on connection 4 1298 1529039481 orexec 0 DATASTEP
CREATE TABLE fram.ORACLE_DATE_DATA(load_type VARCHAR2 (20 CHAR ),sas_date_field DATE) 1299 1529039481 orexec 0 DATASTEP
  1300 1529039481 orexec 0 DATASTEP
  1301 1529039481 orins 0 DATASTEP
ORACLE_314: Prepared: on connection 4 1302 1529039481 orins 0 DATASTEP
INSERT  INTO fram.ORACLE_DATE_DATA (load_type,sas_date_field) VALUES (:load_type,TO_DATE(:sas_date_field,'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')) 1303 1529039481 orins 0 DATASTEP
  1304 1529039481 orins 0 DATASTEP
NOTE: There were 1 observations read from the data set WORK.TEST_DATE.
      WHERE load_type='Data Step';
  1305 1529039481 oruexen 0 DATASTEP
ORACLE_315: Executed: on connection 4 1306 1529039481 oruexen 0 DATASTEP
INSERT statement  ORACLE_314 1307 1529039481 oruexen 0 DATASTEP
  1308 1529039481 oruexen 0 DATASTEP
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* 1309 1529039481 orforc 0 DATASTEP
NOTE: The data set FRAM.ORACLE_DATE_DATA has 1 observations and 2 variables.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* 1310 1529039481 orforc 0 DATASTEP
NOTE: DATA statement used (Total process time):
      real time           0.15 seconds
      cpu time            0.01 seconds
      

61         
62         /*  Bulkload to Oracle using BL_DIRECT_PATH=YES  */
63         
64         %let bl_options1 = %str( BL_SQLLDR_PATH="/apps/oracle/product/12.1.0.2/client/bin/sqlldr"
65                                  BL_RETURN_WARNINGS_AS_ERRORS=YES
66                                  BL_DELETE_FILES=YES
67                                  BL_LOAD_METHOD=APPEND
68                                  BULKLOAD=YES
69                                  BL_DEFAULT_DIR="/data/saswork/tmp/"
70                                  BL_DIRECT_PATH=YES );
71         
72         proc append base = fram.oracle_date_data( &bl_options1 )
  1311 1529039481 orprep 0 APPEND (
ORACLE_316: Prepared: on connection 3 1312 1529039481 orprep 0 APPEND (
SELECT * FROM fram.ORACLE_DATE_DATA 1313 1529039481 orprep 0 APPEND (
  1314 1529039481 orprep 0 APPEND (
  1315 1529039481 orprep 0 APPEND (
ORACLE_317: Prepared: on connection 4 1316 1529039481 orprep 0 APPEND (
SELECT * FROM fram.ORACLE_DATE_DATA 1317 1529039481 orprep 0 APPEND (
  1318 1529039481 orprep 0 APPEND (
73                     data = test_date( where = ( load_type = "BL_DIRECT_PATH=YES" ) );
74         run;

NOTE: Appending WORK.TEST_DATE to FRAM.ORACLE_DATE_DATA.
ORACLE: /apps/oracle/product/12.1.0.2/client/bin/sqlldr USERID="fram"/XXXXXXXXXXXXX@fcsu.world CONTROL="'/data/saswork/tmp/BL_ORACLE_DATE_DATA_17.ctl'" LOG="'/data/saswork/tmp/BL_ORACLE_DATE_DATA_17.log'" 
BAD="'/data/saswork/tmp/BL_ORACLE_DATE_DATA_17.bad'" DISCARD="'/data/saswork/tmp/BL_ORACLE_DATE_DATA_17.dsc'"  1319 1529039481 bulkli 0 APPEND (
NOTE: There were 1 observations read from the data set WORK.TEST_DATE.
      WHERE load_type='BL_DIRECT_PATH=YES';
NOTE: 1 observations added.
NOTE: The data set FRAM.ORACLE_DATE_DATA has . observations and 2 variables.

************** Begin:  SQL*Loader Log File **************

SQL*Loader: Release 12.1.0.2.0 - Production on Fri Jun 15 00:11:21 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   /data/saswork/tmp/BL_ORACLE_DATE_DATA_17.ctl
Data File:      /data/saswork/tmp/BL_ORACLE_DATE_DATA_17.dat
  File processing option string: "FIX 40                                                          "
  Bad File:     /data/saswork/tmp/BL_ORACLE_DATE_DATA_17.bad
  Discard File: /data/saswork/tmp/BL_ORACLE_DATE_DATA_17.dsc 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 1000000
Continuation:    none specified
Path used:      Direct

Table FRAM.ORACLE_DATE_DATA, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
"LOAD_TYPE"                          1:20    20           CHARACTER            
    NULL if "LOAD_TYPE" = BLANKS
"SAS_DATE_FIELD"                    21:39    19 O(X00)      DATE DD/MM/YYYY:HH24:MI:SS
    NULL if "SAS_DATE_FIELD" = BLANKS


Table FRAM.ORACLE_DATE_DATA:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

  Date cache:
   Max Size:      1000
   Entries :         1
   Hits    :         0
   Misses  :         0

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Fri Jun 15 00:11:21 2018
Run ended on Fri Jun 15 00:11:22 2018

Elapsed time was:     00:00:00.36
CPU time was:         00:00:00.03

************** End:    SQL*Loader Log File **************

NOTE: 
**********************************************************************
 Please look in the SQL*Loader log file for the load results.
 SQL*Loader Log File location(available only if BL_DELETE_FILES=NO was set) : -- /data/saswork/tmp/BL_ORACLE_DATE_DATA_17.log --
 Note: In a Client/Server environment, the Log File is located on the Server. The log file is also echoed in the Server SAS log file. 
**********************************************************************
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.61 seconds
      cpu time            0.02 seconds
      

75         
76         /*  Bulkload to Oracle using BL_DIRECT_PATH=YES and BL_API_BULKLOAD=YES  */
77         
78         %let bl_options2 = %str( BULKLOAD=YES
79                                  BL_LOAD_METHOD=APPEND
80                                  BL_API_BULKLOAD=YES
81                                  BL_DIRECT_PATH=YES
82                                  INSERTBUFF = 20000     );
83         
  1320 1529039482 orprep 0 APPEND (
ORACLE_318: Prepared: on connection 3 1321 1529039482 orprep 0 APPEND (
SELECT * FROM fram.ORACLE_DATE_DATA 1322 1529039482 orprep 0 APPEND (
  1323 1529039482 orprep 0 APPEND (
  1324 1529039482 orprep 0 APPEND (
ORACLE_319: Prepared: on connection 4 1325 1529039482 orprep 0 APPEND (
SELECT * FROM fram.ORACLE_DATE_DATA 1326 1529039482 orprep 0 APPEND (
  1327 1529039482 orprep 0 APPEND (
84         proc append base = fram.oracle_date_data( &bl_options2 )
85                     data = test_date( where = ( load_type = "BL_API_BULKLOAD=YES" ) );
86         run;

NOTE: Appending WORK.TEST_DATE to FRAM.ORACLE_DATE_DATA.
*************************************************************
*                 API Bulkload  log                         *
*************************************************************
TABLE: ORACLE_DATE_DATA
SCHEMA: fram
   Number to Load: ALL
   Number to skip: 0
   Errors Allowed: 0
   Skip Index Maintenance: YES
   Skip Unusable Indexes: YES
   Column array rows : 20000
   Column array columns : 131072
   Stream buffer bytes  :256000
NOTE: There were 1 observations read from the data set WORK.TEST_DATE.
      WHERE load_type='BL_API_BULKLOAD=YES';
NOTE: 1 observations added.
NOTE: The data set FRAM.ORACLE_DATE_DATA has . observations and 2 variables.
         1 Rows successfully loaded.
         0 Rows not loaded due to data errors.
*************************************************************
*                 API Bulkload  log  END                    *
*************************************************************
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.17 seconds
      cpu time            0.03 seconds

TomKari · Posted 06-15-2018 08:10 AM

You've done a terrific job of setting up the demonstration. But can I suggest that you put the code that demonstrates the problem with the Oracle tables into your code stream, and output the values to the log? Your can use the PUTLOG statement.

Most people (including me) won't open Office documents from the web, because of the malware threat.

Tom

KenMac · Posted 06-15-2018 08:17 AM

Thanks Tom - good point about the word doc - wish there was a simple way to paste a screenshot.

For now, close your eyes and imagine a Oracle table with three rows and the date value for last one is NULL.

Will update the post shortly.

Thanks

Ken

Tom · Posted 06-15-2018 08:38 AM

@KenMac wrote:
Thanks Tom - good point about the word doc - wish there was a simple way to paste a screenshot.

For now, close your eyes and imagine a Oracle table with three rows and the date value for last one is NULL.

Will update the post shortly.

Thanks

Ken

To paste a screen shot just click on the Photo icon on the menu bar and paste it into the pop-up.

TomKari · Posted 06-15-2018 03:25 PM

I had a few minutes, so I looked at your example in a little more detail, and I can't see any obvious reason for the issue you're reporting.

If you don't receive a good answer from someone on this forum, I wouldn't have any qualms about reporting this to SAS Tech Support. If you do, make sure you let us know what the resolution is!

Tom

KenMac · Posted 06-15-2018 03:31 PM

Read my mind: [SAS 7612473193] (just in case anyone from SAS happens by).

I'll post the solution once I find out.

Ken

Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

Re: Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

Re: Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

Re: Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

Re: Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL

Re: Oracle BL_API_BULKLOAD=YES sets valid datetime fields to NULL