Date:         Sat, 23 Aug 2003 15:49:21 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Newbie: Limiting Available Oracle Tables
Comments: To: ohio11 <ohio11@HOTMAIL.COM>
Content-Type: text/plain

Most experienced SAS users do not use the SAS Display Manager tools that SAS
still ships with the SAS System. Once one has established an ODBC connection
to an external database, the SAS SQL dictionary.tables and
dictionary.columns metadata source views provide names of tables and columns
in the external database. For example,

proc sql;
  select * from dictionary.tables where UPCASE(LIBNAME)='LIB5' and
MEMTYPE='DATA'
  ;
quit;

In place of LIB5, the programmer can enter the SAS library reference (a
pointer to the database). If created by a SAS LIBNAME statement, it should
have this form:

libname NAS ODBC ...

'NAS' is the lib ref.

The MEMTYPE='DATA' condition limits the view of tables to those containing
data. Once one has names of tables, the dictionary.columns view provides
column labels and types (the WHERE conditions UPCASE(LIBNAME)='<lib ref>'
and UPCASE(MEMNAME)='....' limit column information to those in the table
name that replaces the dots and greatly accelerates processing.

Access to a database's metadata via ODBC makes it easy to use the same ODBC
connection to access data from tables: in SQL, SELECT <column names> FROM
<lib ref>.<table name> .... Etc.

Sig

-----Original Message-----
From: ohio11 [mailto:ohio11@HOTMAIL.COM]
Sent: Friday, August 22, 2003 11:22 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Newbie: Limiting Available Oracle Tables


Hello,

I am new to SAS.  In fact, I'm actually an Oracle DBA trying to help one of
my SAS users.

His problem is that when he brings up the window 'SQL QUERY TABLES', it
takes a large amount of time for all of the available tables to be loaded.
Related to this problem is the fact that there are a lot of other Oracle
objects other than TABLES and VIEWS that show up in this list (stored
procedures, function, Java classes, etc.)

Is there a setting in SAS to limit the objects that are retrieved?

He is using SAS version 8.2 on Windows 2000 Pro
We are using Oracle version 8.1.7.2.0
He is connecting to the database using ODBC.

Thanks for any help or links.