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 13:20:44 -0400
Reply-To:     Steve Raimi <steven.raimi@GM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Steve Raimi <steven.raimi@GM.COM>
Subject:      Re: find oracle database tables from Proc SQL

On Tue, 24 May 2005 17:27:49 +0300, Hunter <huntertaylor@SURFEU.FI> wrote:

>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 "" 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 = = 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 >(b) my oracle server database name myDatabase >(c) I have admin userid and password to this database (myID, password) > >libname mydb oracle (server=;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;

I've taken my working code and replaced the specifics per your example:


[in SAS]

libname mydb oracle user=myID password=<your password here> path='MYSERVER.MYDOMAIN.COM' schema='specify what schema you want here';

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

HTH, Steve Raimi

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