BookmarkSubscribeRSS Feed
spoorthy
Calcite | Level 5

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!

3 REPLIES 3
DBailey
Lapis Lazuli | Level 10

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.

spoorthy
Calcite | Level 5

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;

DBailey
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 8247 views
  • 0 likes
  • 2 in conversation