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:
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
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 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.
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
Read my mind: [SAS 7612473193] (just in case anyone from SAS happens by).
I'll post the solution once I find out.
Ken
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.