LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2005, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 24 May 2005 17:27:49 +0300
Reply-To:     Hunter <huntertaylor@SURFEU.FI>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Hunter <huntertaylor@SURFEU.FI>
Subject:      Re: find oracle database tables from Proc SQL
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

Steve Raimi wrote: > You're specifying mydb as the libname for the Oracle connection, so the > last line of your query would be "where libname = 'MYDB' ". If you change > that, the sample code should work for you.

ok, please allow me to recap this then as I still am missing some piece of information

(1) my "tnsnames.org" looks like this, where the "SERVICE_NAME" is the actual name of the database on oracle server

MYSERVER.MYDOMAIN.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = myDatabase) ) )

(2) I do not understand how the sample below (and it does not work) connects to the database names "myDatabase" as it is not being referenced in the libname or the proc sql

(3) In a SQL server libname, I would specify a DSN or I would specify DATABASE, there is no such statement in the oracle libname below, is that why this does not work? (Note: I have no trouble reading SQL server databases and tables)

to recap (a) my oracle server IP address 123.123.123.123 (b) my oracle server database name myDatabase (c) I have admin userid and password to this database (myID, password)

libname mydb oracle (server=123.123.123.123;user=myID,password=password); *note: the database name is not specified - why not?;

proc sql; select mem_name, engine from dictionary.members where libname = 'MYDB'; quit;

(4) If the above starts working, how is a sample table being accessed, for example, I have inside the database myDatabase, a table named myTable ?

assuming mem_name lists myTable as one of the tables, would it look something like

proc sql; select * from MYDB.myTable ; quit;


Back to: Top of message | Previous page | Main SAS-L page