Generate Create table script from view

Hi All,

we have 2 oracle 8i databases let us call them as
source database,target database.

Target database is kind of datawarehouse stuff.

we need to transfer some of the data from source
database to target database.

For that purpose we created views on source database
and new tables (corresponding to each

view) on target database to hold this data.

Now we find that there are some fields whose data type
and field lengths on target database

table are mismatching with source database views.

we have 50 views in source database .so obviously 50
tables on target database.

So my task is to findout those mismatches.

How can you find out those mismatches
programatically???


I am thinking as below.

1.Generate Create table script from source view (by
reading datatype,length from source

view tables)
Script should accept all view names as a string and it should generate create table statements corresponding to each view.





2.Generate create table script from target database
tables.

3.Compare the script using file compare utilities.

what do you guys think?

I need help for task 1 above.
It is kind of very urgent and needs to be finished
asap.

I know in sql server all views columns data types stored in table_schema table.
But in orace where it stores??

Any kind of help is greatly appreciated.


Thank you
--Anil

Create a db link between both databases and compare specs from both {ALL/DBA/USER}_TAB_COLUMNS :rolleyes:

Quote:

---

Script should accept all view names as a string and it should generate create table statements corresponding to each view.

---

From view definition how do you generate table creation script?

Tamil

If you create the database link, and supposing the view names are like "{table_name}_VW", then you can use a script like this to report discrepancies:

Code:

---

Select S.Table_Name, S.Column_Name, S.Column_Id
    , S.Data_Length||'<>'||T.Data_Length Data_Length
    , S.Data_Precision||'<>'||T.Data_Precision Data_Precision
    , S.Data_Scale||'<>'||T.Data_Scale Data_Scale
    , S.Nullable||'<>'||T.Nullable Nullable
  From All_Tab_Columns S, All_Tab_Coluns@Target T
Where T.Owner                = S.Owner 
  And T.Table_Name        = Substr(S.Table_Name,1,Instr(S.Table_Name,'_Vw')-1)
  And T.Column_Id        = S.Column_Id 
  And T.Column_Name        = S.Column_Name 
  And (
      S.Data_Length        != T.Data_Length 
    Or S.Data_Precision        != T.Data_Precision 
    Or S.Data_Scale        != T.Data_Scale 
    Or S.Nullable        != T.Nullable );

---

:D