02-25-2014 03:27 PM
I would like to setup my SAS to be able to PROC APPEND with the bulk option to Oracle.
SAS is on a SOLARIS UNIX Server.
ORACLE is on a different SOLARIS UNIX Server.
From what I understand, the bulk load will use in the background sqlloader. For it to work, what kind of network rights to I need to have? I've been trying unsuccessfully to find documentation on the subject.
I'm guessing that I need to:
1. Include in my PATH the location where the sqlloader is.
2. Have the necessary rights to execute sqlloader myself.
I need more concrete details for this.
02-25-2014 04:06 PM
What is missing from the SAS/access documentation. ? SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition
Installing the oracle client and configuring the installation should work. (page20 ...) you need the SAS access/Ora license. http://support.sas.com/documentation/installcenter/en/ikfdtnwx6cg/66385/PDF/default/config.pdf
02-25-2014 09:26 PM
Agree with Jaap, the docs on SAS/ACCESS to Oracle are your best place to start. THere are also of course plenty of user's group papers (lexjansen.com).
I actually tried my first bulk upload to Oracle a couple weeks ago. I didn't use PROC APPEND, but with a data step the syntax was just:
data Oracle.Table (BulkLoad=YES BL_DEFAULT_DIR="%sysfunc(pathname(work))"); set Have; run;
This was SAS running on a Linux server. When I didn't specify the Bulk Load Defaul Directory, SAS triend to write the temporary files to a directory on the server where I didn't have permission to write. But with the above code, things went fine. For small datasets I got more benefit from playing with InsertBuff option instead of bulk load, but the benefits of buld load seemed to grow with file size. So probably worth playing with both option to see which gives you the best performance.