BookmarkSubscribeRSS Feed
gyambqt
Obsidian | Level 7

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?

 

 

 

 

 

7 REPLIES 7
SASKiwi
PROC Star

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.

gyambqt
Obsidian | Level 7
Yes loading without bulk load is working perfect
SASKiwi
PROC Star

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. 

gyambqt
Obsidian | Level 7
Ty
Tom
Super User Tom
Super User

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?

gyambqt
Obsidian | Level 7
They are all character with 5000 lengths. All same type
SASKiwi
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 820 views
  • 1 like
  • 3 in conversation