11-22-2014 08:58 PM
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!
11-22-2014 10:04 PM
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";
11-23-2014 02:57 AM
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.
11-23-2014 05:54 PM
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:
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!