DATA Step, Macro, Functions and more

Writing to Oracle database using ODBC connection

Reply
Occasional Contributor
Posts: 10

Writing to Oracle database using ODBC connection

I have SAS 9.3 on a PC, it is connecting to the database using ODBC connection.  I can read tables easily enough using the pass through facility but when I try to write using a data step, it is taking a long time and then terminating the connection.  None of the searches on web led me to an example that shows how to write to ODBC using the pass through facility.  If anyone has any examples I can use, I would appreciate it.

Thanks.

Super Contributor
Posts: 275

Writing to Oracle database using ODBC connection

Hi Payal,

I would think this to be the syntax you can use while updating the tables in Oracle -

PROC SQL; *replace lowercase font with your tables/connection info

CONNECT TO myconn (USER =oracleuserid PASSWORD=oraclepw  PATH=oraclepath);

CREATE TABLE TEST AS AS SELECT * FROM myconn

     (

UPDATE schema_name.table_name alias

SET alias.column1=value, alias.column2=value2,...

WHERE alias.some_column=some_value

);

DISCONNECT FROM myconn;

QUIT;

However, it has been some time since I have tried an UPDATE (since you are asking about writing to the database). So, please always check with your local admin. Also, I am not sure if the syntax is different for 9.1.3 vs 9.1.

Thanks,
saspert.

Occasional Contributor
Posts: 10

Re: Writing to Oracle database using ODBC connection

Thank you for answering.

I don't think this solution will work for me.  The problem is that my data is in a SAS dataset (i.e. work.datasetname) and it has millions of rows so I don't think I can just use update for one row at a time.  I used the following syntax but that's where I got the timing error:

Data SCHEMA.test;

  Set work.test;

Run;

  I am hoping for something that will let me do the following (albeit work.test is not recognized in Oracle):

proc sql;

   connect to odbc(user=xxx password=xxx! dsn=Database INSERTBUFF=3000);

   execute (

   create table Schema.test as

   select id, amount, date1

     from work.test

  ) by odbc;

quit;

Super User
Posts: 19,876

Re: Writing to Oracle database using ODBC connection

I thought when you used pass-through you needed to use SQL code, not SAS?

Ie use 'into' rather than 'create table as'

select id, amount, date1

into schema.test

from work.test

You also don't need to do it through the pass through facility.

See this paper, its for Teradata but maybe something similar for Oracle?

http://support.sas.com/resources/papers/proceedings11/105-2011.pdf

Occasional Contributor
Posts: 10

Re: Writing to Oracle database using ODBC connection

Create table as is part of SQL code, so I tried to use that.  Per your suggestion, I tried the into, but it gave me the following error:

ERROR 79-322: Expecting a :.

ERROR 76-322: Syntax error, statement will be ignored

When I researched more, it seemed like into: is creating a macro variable rather than writing to a table.  Still, no resolution for me.  The reason I am using the pass through facility is because the database resides at a third party vendor location and I have to use ODBC to connect to it anyway.

Thank you for the suggestions though.  The paper you linked was very interesting.

Super User
Posts: 19,876

Re: Writing to Oracle database using ODBC connection

Ok...could you post the code you used?

You can also reference this paper:

www2.sas.com/proceedings/sugi29/106-29.pdf

specifically on how to load tables into Oracle, its old, but perhaps some of those methods would work for you.

I usually use a libname statement with an odbc connection and it works fine...

Super Contributor
Posts: 275

Re: Writing to Oracle database using ODBC connection

From my past experience I have seen it is quite cumbersome to load Oracle using Libname statment because there is usually less control over a dataset having 1 million rows. And on the other hand using Proc SQL Pass thu will allow only 1 record to be updated at a time.

Any other users who have faced similiar issues?

Occasional Contributor
Posts: 10

Re: Writing to Oracle database using ODBC connection

Reeza - I was able to use the paper you linked and it worked very well.  Here's my code:

libname mylib odbc user=xxx password=xxx dsn=DSNNAME insertbuff=32767;

proc sql;

  create table mylib.trans_test as

  select * from work.trans

quit;

Roughly 13.5 million records were written in 30 minutes.  Much better than what I was getting previously.  Thank you for all your help.

Ask a Question
Discussion stats
  • 7 replies
  • 396 views
  • 3 likes
  • 3 in conversation