Generate Create table script from view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Generate Create table script from view

  1. #1
    Join Date
    Oct 2005
    Posts
    1

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool


    Create a db link between both databases and compare specs from both {ALL/DBA/USER}_TAB_COLUMNS
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool


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


    Last edited by LKBrwn_DBA; 10-13-2005 at 12:15 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width