How to connect to oracle database-'path'question

Reply
Contributor
Posts: 21

How to connect to oracle database-'path'question

Hi everyone:

I use sas 9.4,I want to connect it to oracle database,I read some support documents,I noticed that in general it needs three parameters:user,orapw and path,but

I don't know how to fill the "path" blank,thank you very much!

Super User
Posts: 17,819

Re: How to connect to oracle database-'path'question

I find it easier to define a DSN in the user definition first that stores your password etc and you can test the connection there. Then define it in SAS using ODBC connection:

libname orac dsn="my_db" schema="dbo";

Contributor
Posts: 21

Re: How to connect to oracle database-'path'question

Thank you!

Super User
Posts: 3,105

Re: How to connect to oracle database-'path'question

There are several steps you need to confirm to connect to Oracle:

1) Do you have either SAS/ACCESS to Oracle or SAS/ACCESS to ODBC installed and licensed in your SAS 9.4? How you connect to Oracle is a little different depending on which one you have. If you don't have either of these you will not be able to connect to Oracle.

2) Do you have the Oracle client tools installed - SQL*Net etc? Without these you will not be able to connect to Oracle either. Also if you are using ODBC you will need the Oracle ODBC driver installed which I suspect comes with the client tools.

3) The path parameter you need is defined in an Oracle file called TNSNAMES.ORA. This is a text file that comes with SQL*NET and it contains definitions for all of the Oracle databases you need to connect to. This would normally be set up by an Oracle DBA as a company-wide standard. I suggest you contact your Oracle DBA to confirm your path string, or see if you can find the database you want listed in TNSNAMES.ORA (you will find this in the Oracle tools install directory).

4) If you can query Oracle through the SQL-PLUS tool successfully then you are set up to also use SAS as long as requirement 1) is satisfied.

Contributor
Posts: 21

Re: How to connect to oracle database-'path'question

Thank you!

Contributor
Posts: 21

Re: How to connect to oracle database-'path'question

Hello,I have solved the problem,for I use win 8.1 system,so just go to CONTROL PANEL-System and Security-Administrative Tools-ODBC DATA SOURCE-System DSN-Configure,then use the information there and code below:

proc sql;

connect to odbc

(dsn=***** uid=*** pwd=****);

(your sql code)

'*'parts' information is from configuration above.

Hope this can help others who are new to this like me.

And many thanks for Reeza and SASKiwi's reply!Thank you,guys!

Ask a Question
Discussion stats
  • 5 replies
  • 2176 views
  • 1 like
  • 3 in conversation