I am connecting to an Oracle DB via tan ODBC driver in SAS.
LIBNAME tables
ODBC
USER=USER
PASSWORD=*****
DSN="Name"
SCHEMA=SCEMA;
The Oracle database has some tables replicated as a materialized view to speed up processing for some users. When I connect with SAS does the library show a copy of the table, or the materialized view of the table? I would prefer not to use the materialized version as it updates less frequently than I need it.
Googling with keywords: oracle materialized view and table with same name
You cannot create a table and a materialized view of the same name. However, when you create a materialized view, Oracle automatically creates a table by the same name in the ... _objects data dictionary views. When you select from that, you are selecting from the materialized view.
The materialized view (which actually is a table loaded into memory) is the result of a SQL query. It will retrieve the data directly or indirectly from table(s). I guess you could execute a describe on the view to get to the table names (to which you might or might not have read access). If you've got the read access then you could use the query from the describe in your own code (not sure actually: You need eventually to use the DDL of the view to get to the query which loads the data).
But... looking into Oracle docu here if and how complicated it will get for you to query the underlying physical table(s) directly will depend on the view definition. And you'll likely also experience a big performance degradation if directly querying the underlying physical tables.
Is there a difference in the name of the table versus the view? If so both should show. What do you see if you run PROC CONTENTS on the library?
Googling with keywords: oracle materialized view and table with same name
You cannot create a table and a materialized view of the same name. However, when you create a materialized view, Oracle automatically creates a table by the same name in the ... _objects data dictionary views. When you select from that, you are selecting from the materialized view.
The materialized view (which actually is a table loaded into memory) is the result of a SQL query. It will retrieve the data directly or indirectly from table(s). I guess you could execute a describe on the view to get to the table names (to which you might or might not have read access). If you've got the read access then you could use the query from the describe in your own code (not sure actually: You need eventually to use the DDL of the view to get to the query which loads the data).
But... looking into Oracle docu here if and how complicated it will get for you to query the underlying physical table(s) directly will depend on the view definition. And you'll likely also experience a big performance degradation if directly querying the underlying physical tables.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.