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 "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;

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

[in tnsnames.ora]
MYSERVER.MYDOMAIN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myDatabase)
    )
  )


[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