How to enable Bulk to Oracle

Reply
Occasional Contributor
Posts: 8

How to enable Bulk to Oracle

Hi everyone,

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.

Thanks!

Trusted Advisor
Posts: 3,215

Re: How to enable Bulk to Oracle

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

---->-- ja karman --<-----
Super Contributor
Posts: 644

Re: How to enable Bulk to Oracle

It's my recollection that at least on SAS 8.x both SAS and Oracle have to reside on the same server to enable bulk load. 

Richard

PROC Star
Posts: 1,324

Re: How to enable Bulk to Oracle

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.

Ask a Question
Discussion stats
  • 3 replies
  • 223 views
  • 0 likes
  • 4 in conversation