views missing table fields 2

[jwa6]

oracle 10g

I have views on tables in oracle that I need to check to see if the views are current.
Sometimes we add a new field to a table and recompile the view.
But, the view wasnt changed to reflect the added field in the table.

this is on our dev DB... We have many contractors thru here and I think over time this has become and issue. I have found 2 last week. Is there a sql I can write that I can over from time to time to find the missing fields in the views?

 

[Turkbear]

Hi,
Were the views constructed with explicit column statements ( Create or Replace view test_view as select field1,field2 from my_table) or with a Select * from table(s) command?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

You just need something like:

select t1.table_name, t1.column_name 
from user_tab_columns t1, user_tab_columns t2
where T1.TABLE_NAME = 'PERSON' and T2.TABLE_NAME(+) = 'PERSVIEW'
and T1.COLUMN_NAME = T2.COLUMN_NAME (+)
and t2.column_name is null

It does of course assume that you have used the same names for columns in the views as for tables.

For Oracle-related work, contact me through Linked-In.

 

[jwa6]

yes

this is the approach we have here...

Create or Replace view test_view as select field1,field2 from my_table)

Im not sure how your solution here works( but thanks for the response by the way)

Lets say I have only one table in the view and the view is missing added fields in the table. How do I find out what fileds are missing in the view?

jim

 

[SantaMufasa]

Perhaps the simplest way to compare the structures of two different data sets (tables or views) is:

select column_name from user_tab_columns where table_name = '<name of table that has the "Gold-Standard" columns>'
minus
select column_name from user_tab_columns where table_name = '<name of  the [B][I]VIEW[/I][/B] to compare to the "Gold-Standard" columns>'
/

Note: USER_TAB_COLUMNS is a Data Dictionary object that contains the names of each Table's or View's columns. You can "DESCRIBE USER_TAB_COLUMNS" just as you can any other table.

Let us know if this is helpful.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

My approach should work perfectly with the way your views are set up (that was exactly what I tested it with).

For Oracle-related work, contact me through Linked-In.

 

[jwa6]

Hello

I did try the minus from user_tab_columns as suggested.

The sql pulled all of the fields from the table.
Not just the 3 missing in the view.

I then added all the fields from the table to the view
The results from the sql were the same as before.

All fields selected from the table

jim

 

[SantaMufasa]

jwa6, Please copy and paste your "minus" query here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jwa6]

select column_name from user_tab_columns where table_name = 'afact'
minus
select column_name from user_tab_columns where table_name = 'vw_afact'


THERE ARE 3 FIELDS IN THE TABLE THAT ARE NOT IN THE VIEW


CREATE or replace view vw_afact (
nsc_ps_enr_fact_key ,
nsc_submission_key ,
nsc_return_key ,
lds_student_key ,
curr_lds_student_key ,
hs_fact_key ,
hs_comp_highest_cred ,
hs_comp_highest_cred_exit_date ,
hs_comp_highest_cred_year ,
hs_comp_highest_cred_ye_key ,
dist_acctbl_agency_key_ye ,
dist_acctbl_code_ye ,
sch_acctbl_agency_key_ye ,
sch_acctbl_code_ye ,
nsc_record_found_ind ,
ps_inst_code ,
ps_enr_begin ,
ps_enr_end ,
ps_enr_status_code ,
ps_enr_prior_ind ,
ps_enr_following_fall_ind ,
ps_enr_2nd_fall_ind ,
ps_grad_ind ,
ps_grad_date ,
ps_degree_title ,
ps_major ,
ps_inst_seq_attended ,
--file_source_date ,
--file_submit_date ,
--file_return_date ,
create_date
)
as
select
nsc_ps_enr_fact_key ,
nsc_submission_key ,
nsc_return_key ,
lds_student_key ,
curr_lds_student_key ,
hs_fact_key ,
hs_comp_highest_cred ,
hs_comp_highest_cred_exit_date ,
hs_comp_highest_cred_year ,
hs_comp_highest_cred_ye_key ,
dist_acctbl_agency_key_ye ,
dist_acctbl_code_ye ,
sch_acctbl_agency_key_ye ,
sch_acctbl_code_ye ,
nsc_record_found_ind ,
ps_inst_code ,
ps_enr_begin ,
ps_enr_end ,
ps_enr_status_code ,
ps_enr_prior_ind ,
ps_enr_following_fall_ind ,
ps_enr_2nd_fall_ind ,
ps_grad_ind ,
ps_grad_date ,
ps_degree_title ,
ps_major ,
ps_inst_seq_attended ,
-- file_source_date ,
-- file_submit_date ,
-- file_return_date ,
create_date
from
afact



JIM

 

[SantaMufasa]

select column_name from user_tab_columns where table_name = 'afact'
minus
select column_name from user_tab_columns where table_name = 'vw_afact';

By default, Oracle creates and stores object names as UPPERCASE characters, regardless of the case you create the objects:

create table jwa (x number, y number, z varchar2(20));

select table_name, column_name from user_tab_columns
 where table_name = 'jwa';

no rows selected

select table_name, column_name from user_tab_columns
 where table_name = 'JWA';

TABLE_NAME  COLUMN_NAME
----------- -----------
JWA         X
JWA         Y
JWA         Z

The only time that Oracle does not store an object name in uppercase is if you override Oracle's default and define the name within double quotes and then ALWAYS and FOREVER after use double quotes to surround the name of the non-default spelling!!! (What a hassle, and certainly not considered "good form" in the Oracle World.):

create table "jwa" ("x" number, y number, "z" varchar2(20));

Table created.

select table_name, column_name from user_tab_columns
 where table_name = 'JWA';

no rows selected

select table_name, column_name from user_tab_columns
 where table_name = 'jwa';

TABLE_NAME  COLUMN_NAME
----------- -----------
jwa         x
jwa         Y
jwa         z

SQL> select * from jwa;
select * from jwa
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "jwa";

no rows selected

Notice these issues: [ul][li]Because of my (inconsistent) use of double quotes to define the table and columns, the column names are mixed upper- and lower-case characters (x, Y, z).[/li][li]Whenever I refer to tables or columns that I defined with double quotes, I must use double quotes and use the original case to refer to the objects.[/li][/ul] Overriding Oracle's default behavior by using double quotes to define columns is a terrible practice since it introduces such a high risk for coding problems in Oracle.


Notice in your code that you posted, above, you asked Oracle to find objects that are spelled 'afact' and 'vw_afact'. Unless you created the objects within double quotes and used lower case, Oracle will not find them.

Apparently, you created 'afact' using double quotes and lower case (since Oracle found 'afact' columns), but notice in your "CREATE VIEW..." code, you did not use double quotes, thus Oracle creates the view as upper case...And 'VW_AFACT' certainly is not equal to 'vw_afact'.

Let us know if this resolves your issues after correcting this scenario.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jwa6]

select column_name from user_tab_columns where Upper(table_name) = 'AFACT'

MINUS

select column_name from user_tab_columns where Upper(table_name) = 'VW_AFACT'
/


THIS DID find the 3 fields missing in the view.

this is great.

But this leads to another question. For every table and view
I want to check Ill need to go one by one to see what view Ill need to change.

A looooooooooong process.

IS there a way to do this by schema owner?

All owner tables to all views on that table?

jim

and thanks for the suggestions above

 

[SantaMufasa]

Jim,

You should be able to write a SQL query that generates all of your "minus" queries. I would have generated a sample "SQL-writing-SQL" query to get you started, but first I'd need to know a bit more about needs.

Remember that VIEWs (since they are simply "named SQL queries") can have multiple tables that are involved. What are you going to "match" against if your VIEW is a multi-table join?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

You could fairly easily adapt my query (which is why I wrote it that way rather than using a MINUS). You would just need something like:

select tc.table_Name, tc.column_name
from (select u.table_name, v.view_name, uc.column_name 
      from user_tables u, 
           user_views v,
           user_tab_columns uc
      where 'VW_'||u.table_name = v.view_name
       and u.table_name = uc.table_name) tc,
     user_tab_columns vc
where tc.view_name = vc.table_name(+)
and tc.column_name = vc.column_name(+)
and vc.column_name is null

There are probably instances where the view_name = 'VW_'||<table_name> doesn't hold true because you've had to shorten the view name, but you could handle those as individual cases. You also have to be logged on as the user which owns the tables (otherwise, you'll have to change the above to all_tables and add owner = <owner of table>).


For Oracle-related work, contact me through Linked-In.

 

[jwa6]

thanks... Dagon for the post

Ill look it over later today and let you know

jim