Help using Base SAS procedures

PROC DBLOAD - Question and Alternatives?

Reply
Trusted Advisor
Posts: 1,301

PROC DBLOAD - Question and Alternatives?

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
Super User
Posts: 10,035

Re: PROC DBLOAD - Question and Alternatives?

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
Regular Contributor
Posts: 151

Re: PROC DBLOAD - Question and Alternatives?

To set the limit add limit= statement before load statement.

limit=1000000;
Trusted Advisor
Posts: 1,301

Re: PROC DBLOAD - Question and Alternatives?

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
Super User
Posts: 5,430

Re: PROC DBLOAD - Question and Alternatives?

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
Trusted Advisor
Posts: 1,301

Re: PROC DBLOAD - Question and Alternatives?

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.
Ask a Question
Discussion stats
  • 5 replies
  • 323 views
  • 0 likes
  • 4 in conversation