BookmarkSubscribeRSS Feed
FriedEgg
SAS Employee
Hi,

I want to load sas datasets into a Oracle database using a specific tablespace. Here is what I am trying:

proc dbload dbms=oracle data=sashelp.bweight(keep=weight black);
user=test_user;
orapw=test_pass;
path=test_db;
table='PROC_DBLOAD_TEST';
tablespace=test_tablespace;
load;
run;


My first problem is the following:

NOTE: Load ended due to limits. Examine statistics below.
NOTE: Limits - Insert (5000), Error (100), Commit (1000)
NOTE: Inserted (5000) rows into table (mk_prod_dbload_test)
NOTE: Rejected (0) insert attempts see the log for details.

I cannot find information on how to set these limits, I also do not understand why there are error's being reported.


Are there alternatives to using the dbload procedure where I can still define a tablespace?

Thanks,
Matt
5 REPLIES 5
Ksharp
Super User
Hi.
I am not sure whether it yield your problem.
As I remeber. proc dbload has limited for loaded rows.Default is 1000 rows (I have forgotten), and there is an option can maximize the default rows.I have forgotten ,You can refer to documentation.



Ksharp
Oleg_L
Obsidian | Level 7
To set the limit add limit= statement before load statement.

limit=1000000;
FriedEgg
SAS Employee
Thanks Oleg_L, I totally blanked on the doc sheet here because it is clear as day now. Any idea of a alternative to this procedure for loading to a given tablespace?

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003361011.htm

http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/access_oracle_7367.pdf Message was edited by: Fried Egg
LinusH
Tourmaline | Level 20
Using the ACCESS LIBANME engine, you can specify tablespace as an option:

http://support.sas.com/kb/5/928.html

The preferred way to load (large) Oracle is usally by using Oracle bulk loader. See the documentation for syntax and examples.

/Linus
Data never sleeps
FriedEgg
SAS Employee
Linus, thanks, this is exactly what I was looking for! I do use the Oracle bulk loading on large data sets (typically several million rows or more) but for smaller tables was looking for this alternative.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1188 views
  • 0 likes
  • 4 in conversation