Good morning,
I am trying to use a SQL Pass through facility to insert SAS dataset values into Oracle and i get the following error
ORA-00942: table or view does not exist.
My syntax looks similar to below
proc sql;
connect to oracle(user=xxxxx password=xxxxx path=xxxxx);
execute(
insert into oracletable as select * from sastable
) by oracle;
disconnect from oracle;
quit;
I am wondering if there is a way to use sas data set to insert into ORACLE using SQL Pass through Facility.
Any help would be appreciated.
Thanks!
You can't reference a sas table within a pass through block. You can create a library and insert records that way.
libname olib oracle user=xxxx password=xxxx path=xxxx;
proc sql;
insert into olib.oracletable (col1, col2...)
select col1,col2.... from sastable;
quit;
There are also bulk insert options available as well.
Thank you for the response. I am familiar with the SAS/ACCESS way of doing it. But my problem was the first column in the oracle table is a sequence generated ID. Is there a way of inserting data into ORACLE using SAS/ACCESS way where the first column is a sequence generated by oracle and the rest of the columns are from a sas data set?
Updated version of my syntax is as below
proc sql;
connect to oracle(user=xxxxx password=xxxxx path=xxxxx);
execute(
insert into oracletable (ID, col1) select * schema.nextval from dual, select col1 from sas dataset
) by oracle;
disconnect from oracle;
quit;
Most of the time, I've seen the oracle column ID value have a default value of the next sequence number so all you would have to do is not include that column in the insert statement. If the column has no default value and you can't add one, I would next look at creating a temp oracle table which would allow you to execute the insert via pass through.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.