topic Re: SAS bulkload issue in SAS Programming

SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 02:36:35 GMT

Hi Experts,

Is there any limitation on number of columns used when bulkload into Oracle?

I am loading a SAS table into Oracle using below:

proc append base = oracle.table2(&oracle_bload_options BULKLOAD = YES
BL_LOAD_METHOD = Truncate )
data =table1 force ;

run;

table1 and Oracle.table2 both have 600 columns

I get the below errors:

ERROR:
**********************************************************************
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) : --
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: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 5.61 seconds
user cpu time 0.04 seconds
system cpu time 0.03 seconds
memory 13503.21k

however if I reduce columns from Oracle.table from 600 to 260 then it is working!

Only first few columns have values and all rest columns are empty

Why is this?

Re: SAS bulkload issue

SASKiwi — Sun, 25 Sep 2022 02:44:36 GMT

Have you tried this without bulk load? If that works then this is definitely a bulk load issue. I suggest you open a Tech Support track to investigate further.

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 04:00:16 GMT

Yes loading without bulk load is working perfect

Re: SAS bulkload issue

SASKiwi — Sun, 25 Sep 2022 05:10:35 GMT

It would be a good idea to talk to one of your Oracle DBAs as well. They should be able to diagnose the BL log files.

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 06:24:36 GMT

Re: SAS bulkload issue

Tom — Sun, 25 Sep 2022 19:32:10 GMT

Is there something different about the variables that your excluded to try the bulk load with fewer variables? Are they using a different data type? Are the names strange in any way?

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 19:47:54 GMT

They are all character with 5000 lengths. All same type

Re: SAS bulkload issue

SASKiwi — Mon, 26 Sep 2022 03:54:37 GMT

One possible cause for the bulk load problem would be lack of space in your Oracle BL folder. This is something your Oracle DBA should be able to check out.