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
