BookmarkSubscribeRSS Feed
Florent
Quartz | Level 8
Dear SAS Experts,

I'd like to know what are the mandatory steps in order to connect to an Oracle DB which is stored on a Unix server from a PC on Windows XP.

We currently have ordered the module SAS Access-To-Oracle for every computer which will need to run the application and it should be installed soon.

I've already read a lot of documentation about the Syntax to use in order to connect to an oracle DB and to execute SQL statements (using pass-through facility or not). This is not difficult to understand and it's not my main concern about it.

In fact, I just wonder if we need to install an Oracle component on the clients (Listeners or something else) or if everything will be managed by the SAS module.

e.g.: What should be the value of the parameter "path" in the following statement in order to access our distant DB ?

libname mydblib oracle user=smith password=secret path='myoracleserver';

I know that it can seem to be a stupid question for some of you but as I'm not able to test anything for the moment, I'd like to win some time by retrieving the needed information in advance.

As usual, Thx for your precious help !

Regards,
Florent
5 REPLIES 5
LinusH
Tourmaline | Level 20
You'll need to have the Oracle SQL*NET client installed on your SAS client machine.
The path could be an DB alias that is specified in the TNSNAMES.ORA file, see this note:

http://support.sas.com/kb/23/865.html

Regards,

Linus
Data never sleeps
Cynthia_sas
SAS Super FREQ
Hi:
If you are using the BI Platform, then the connection to Oracle information, such as path and password, etc, can also be defined in the metadata. You might wish to look at the data management documentation for the Platform, in that case.

cynthia
Florent
Quartz | Level 8
Hi,

We successfully installed the SAS access to Oracle module and succesfully connect to the Oracle database.

Now we have isssues with the execution of a procedure which is stored on Oracle.

Here is the part of the log describing it:
53302 execute(exec p_write_data) by oracle;
ERROR: ORACLE execute error: ORA-06550: line 1, column 8: PLS-00201: identifier 'P_WRITE_DATA' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored.


Would you have any idea why we get this message ? Is it possible to retrieve the available procedures/functions' names stored on the Oracle side ?

Thank you in advance.

Regards,
Florent
LinusH
Tourmaline | Level 20
Executing Oracle stored procedures is covered by this SAS (alraedy mentioned in a different thread):

http://support.sas.com/kb/18/350.html

/Linus
Data never sleeps
deleted_user
Not applicable
Hi,
If a SAS data set contains variable names that are Oracle reserved words, the above error will be returned and The variables will need to be renamed, using names that are not Oracle reserved words.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1396 views
  • 0 likes
  • 4 in conversation