I'm not sure that you can put a select (variable list) from a sastable into an Oracle table Insert statement, unless you actually loaded that sas table to Oracle first. Then you are left with inserting actual values, which can be done with macro variables, but it is a bit tricky, in that you need to use single quotes on the insert in Oracle, and you can't resolve the macro variable inside single quotes; so then you have to get into learning how to quote a macro variable...

If you can actually load the sas table to oracle, probably by doing this: libname myoracle ....; data myoracle.sastable; set work.sastable; run;

Then it might work. Remember on a pass-though SQL that it is behaving like it is being exectuted actually in Oracle itself. If you have SQL Developer or SQL Plus access to Oracle you might want to try your query there first as a test. But I think the problem is that your sastable is a SAS file, not an Oracle table, which won't work on a pass-through query.

-Mary ----- Original Message ----- From: Jack Hamilton To: SAS-L@LISTSERV.UGA.EDU Sent: Tuesday, September 23, 2008 9:40 AM Subject: Re: Update an oracle table from sas where Oracle makes use of a sequence

What happens when you try it?

The syntax you use looks odd to me, but I'm not an Oracle expert, so I can't say for sure that it's wrong. Are you specifying 7 columns in the first clause but only 3 in the second clause? And SASTABLE is an Oracle table?

On Tue, 23 Sep 2008 06:52:04 -0700, "David in the UK" <xfg6u@YAHOO.COM> said: > Does anyone know how to update an Oracle table that makes use of a > sequence, would the following work? > > execute(insert into ORA_TABLE values (sequencename.NEXTVAL, refid1, > refid2, datavalue1, datavalue2, datavalue3, sysdate) as > select datavalue1, datavalue2, datavalue3 > from sastable) by oracle;

-- Jack Hamilton Sacramento, California jfh@alumni.stanford.org <== Use this, not jfh@stanfordalumni.org