topic Re: SAS Access to Oracle - Basic Information needed in Developers

SAS Access to Oracle - Basic Information needed

Florent — Mon, 24 Nov 2008 15:32:58 GMT

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

Re: SAS Access to Oracle - Basic Information needed

LinusH — Mon, 24 Nov 2008 16:04:05 GMT

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

Re: SAS Access to Oracle - Basic Information needed

Cynthia_sas — Mon, 24 Nov 2008 17:57:10 GMT

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

Re: SAS Access to Oracle - Basic Information needed

Florent — Wed, 21 Jan 2009 10:33:16 GMT

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

Re: SAS Access to Oracle - Basic Information needed

deleted_user — Wed, 21 Jan 2009 11:18:02 GMT

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.

Re: SAS Access to Oracle - Basic Information needed

LinusH — Wed, 21 Jan 2009 11:21:54 GMT

Executing Oracle stored procedures is covered by this SAS (alraedy mentioned in a different thread):

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

/Linus