Date:         Wed, 3 Sep 2008 09:43:40 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Oracle Bulk Update
Comments: To: Patrick <patrick.matter@GMX.CH>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

Patrick,

You should be able to execute any Oracle command that you want by putting it
inside an "execute" statement, like this:

proc sql;
connect to oracle (user=user password=pw path=xe);
execute (drop table "OPH_TABLE") by oracle;
execute (create table "OPH_TABLE" as
select * from "mhn_master_numbers"
where "mhn_full_nbr" like '%OPH%')
by oracle;
disconnect from oracle;
quit;


So if you know the command for a bulk insert, you can put it in parenthesis
in an execute statement; sorry, I haven't done a bulk insert in Oracle and
thus don't have the exact command for doing one.

If what you are trying to do is to load a SAS data set to Oracle, sometimes
using a libname statement is easier, such as:

libname myoracle oracle user=user password=pw path=xe
preserve_tab_names=yes;

proc datasets library=myoracle;
delete mds_master_data_set;
run;

data myoracle.master_data_set;
set work.master_data_set;
run;


Note that my "path" above is for Oracle Express (xe); your path will
probably be different.

-Mary


----- Original Message -----
From: Patrick
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, September 02, 2008 11:46 PM
Subject: Oracle Bulk Update


Hi All
I know how to bulk load from SAS to Oracle.
I saw now lately on the Internet commands like bulk update, bulk
insert and bulk collect (often within pl/sql).
Does one of you know if this commands are available from within SAS -
or have even a little sample?
All help appreciated
Patrick