Network connection Oracle APEX to SAS

[pcfriendly]

Hi

I've got an Oracle APEX DB & want to be able to use the tables in my SAS EG 4.3 using Windows XP Pro.

I've tried all sorts of combinations but cannot get the database to appear.

I add the connection strings into SAS through >> Tools >> Options >> SAS Programs >> Submit SAS code when Server is connected.

I already have a 3rd party Oracle db connected but can't work out why I can't get this one to display in my Server library when I start up SAS.

Here's the 3rd party Oracle string that works;

LIBNAME CDR_LIVE ORACLE  PATH=CDRX10.world SCHEMA='CDR1P1' USER='F348' PASSWORD= 'xxxxx';

Here's the Oracle APEX string that doesn't work;

LIBNAME APEXORCL ORACLE SERVER='lxdb0120.in.company.com.au' SCHEMA='DATA' USER='F348' PASSWORD= 'xxxxx';

Can anyone help?

Thanks

[SASKiwi]

It would be helpful if you posted the SAS error messages as well.

[pcfriendly]

There is no error message, the schema just doesn't appear in the Server Libraries list.

[TomKari]

Instead of putting your libname statement in the "submit when server connected" option, put it explicitly into a code window, and submit it to your server. Your log should then show an error message, which you can post to give us some indications of the problem.

Tom

[ChrisHemedinger]

Or, if you're looking for the elusive SAS log content that happens as part of an autoexec, check this technique:

http://blogs.sas.com/content/sasdummy/2012/01/11/uncovering-the-hidden-parts-of-the-sas-log/

Chris

[pcfriendly]

Thanks for the tip TomKari,

I get the error as below;

ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve the connect identifier specified.

ERROR: Error in the LIBNAME statement.

I've tried changing things around but no luck, appreciate any suggests, Thanks

[TomKari]

TNS is an Oracle communication facility. This may be an Oracle connection problem, with nothing to do with SAS.

What I usually suggest to people in this situation is to connect to the Oracle database with an Oracle tool from your SAS server (I'm not familiar with Oracle APEX, but in my environment I would suggest to connect directly to Oracle with SQL*Plus, and make sure you can access your data.) Don't just get a connection, make sure you can actually see data, as this will establish that there aren't any permission problems.

If you can do this, and it's very important that you do it from the machine that you are using as your SAS server, the problem is probably with your SAS connection. Otherwise, you need to get the Oracle part of it working first, then move on to the SAS.

Let us know what happens, and we can continue from there.

Tom

[SASKiwi]

On the SAS server you are using with EG, Oracle's SQL*NET software must be installed and in that software there is a configuration file called TNSNAMES.ORA where all of the Oracle databases you wish to connect to must be defined. The error you are getting indicates that the Oracle server CDRX10 is not defined in this file. Please note TNSNAMES.ORA is simply a text file. You can do a search to find it in the Oracle software directory then open it in NOTEPAD and search for the text CDRX10.  

[Dorota_Jarosz]

This is also true for independent Windows workstation SAS installation trying to connect to an ODBC system database. We had to add servers to tnsnames.ora file on each PC. We also had to define this system database in Windows Data Sources (ODBC) tool.

[pcfriendly]

Hi Everyone who replied,

Firstly, thanks for your suggestions.

In order to follow up the SQL Plus option to check the connectivity of my Oracle APEX DB, I installed 11G which has SQL Plus but I'll need some help with writing the script to test the connection.

I have the SERVER as 'lxdb0120.in.company.com.au', the username & password.

What do I need to write on the command line with SQL Plus to check the connection? Ping?

[SASKiwi]

Unfortunately I don't have Oracle available to me where I am working currently, but when you start SQL Plus you are prompted for the Oracle path username and password - the path resolves to a particular Oracle server and database using the TNSNAMES.ORA file. Have you added the Apex server definition into this file? Also Oracle provides a TNSPING command to see if an Oracle database/server is active or not. BTW you can google these issues to find out more.