BookmarkSubscribeRSS Feed
pcfriendly
Calcite | Level 5


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

10 REPLIES 10
SASKiwi
PROC Star

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

pcfriendly
Calcite | Level 5

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

TomKari
Onyx | Level 15

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
pcfriendly
Calcite | Level 5

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
Onyx | Level 15

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
PROC Star

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
Obsidian | Level 7

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
Calcite | Level 5

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 1922 views
  • 1 like
  • 5 in conversation