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;