1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to validate the table structure

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Aug 12, 2009.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    hi all,
    i have a scenario that i need to insert a data from one table to other which having same structure , before inserting i need to validate both the table having same structure or not, like the colunms are in same order and same name , and data type and datalenght etc,

    and how to know the colunm having the different datatype , etc
    let me know how to achive this...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,142
    Likes Received:
    308
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
     
    Thare are far too many variables to provide a solution for your last requirement of reporting which column/columns aren't an exact match between the tables, however I can provide a solution of reporting which tables are exact matches:

    Code (SQL):
    SQL> CREATE TABLE yazoo (
      2          yonto   NUMBER,
      3          klimdet varchar2(9),
      4          gretlan varchar2(40),
      5          snorpo  NUMBER,
      6          twing   NUMBER
      7  );

    TABLE created.

    SQL>
    SQL> CREATE TABLE darku_orp (
      2          yonto   NUMBER,
      3          klimdet varchar2(8),
      4          gretlan varchar2(40),
      5          snorpo  NUMBER,
      6          twing   NUMBER
      7  );

    TABLE created.

    SQL>
    SQL> CREATE TABLE plabu_yan (
      2          yonto   NUMBER,
      3          klimdet varchar2(9),
      4          gretlan varchar2(40),
      5          snorpo  NUMBER,
      6          twing   NUMBER
      7  );

    TABLE created.

    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10000 loop
      3                  INSERT INTO yazoo
      4                  VALUES(i, 'Val '||i, 'This is a really nice value for '||i, i, i);
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> WITH taba AS (
      2          SELECT TABLE_NAME name_a, column_name||column_id||data_type||data_length||nvl(data_scale,0)||nvl(data_precision,0) col_def_a
      3          FROM user_tab_columns
      4          WHERE TABLE_NAME = UPPER('&1')
      5  ),
      6  tabb AS (
      7          SELECT TABLE_NAME name_b, column_name||column_id||data_type||data_length||nvl(data_scale,0)||nvl(data_precision,0) col_def_b
      8          FROM user_tab_columns
      9          WHERE TABLE_NAME = UPPER('&2')
     10  )
     11  SELECT name_a, name_b
     12  FROM
     13  (SELECT name_a, SUM(instr(col_def_a, col_def_a)) colmtch_a
     14  FROM taba
     15  WHERE instr(col_def_a, col_def_a) > 0
     16  GROUP BY name_a) x,
     17  (SELECT name_b, SUM(instr(col_def_a, col_def_b)) colmtch_b
     18  FROM taba, tabb
     19  WHERE instr(col_def_a, col_def_b) > 0
     20  GROUP BY name_b) y
     21  WHERE colmtch_a = colmtch_b;
    Enter VALUE FOR 1: yazoo
    Enter VALUE FOR 2: plabu_yan

    NAME_A                         NAME_B
    ------------------------------ ------------------------------
    YAZOO                          PLABU_YAN

    SQL>
    SQL> WITH taba AS (
      2          SELECT TABLE_NAME name_a, column_name||column_id||data_type||data_length||nvl(data_scale,0)||nvl(data_precision,0) col_def_a
      3          FROM user_tab_columns
      4          WHERE TABLE_NAME = UPPER('&1')
      5  ),
      6  tabb AS (
      7          SELECT TABLE_NAME name_b, column_name||column_id||data_type||data_length||nvl(data_scale,0)||nvl(data_precision,0) col_def_b
      8          FROM user_tab_columns
      9          WHERE TABLE_NAME = UPPER('&2')
     10  )
     11  SELECT name_a, name_b
     12  FROM
     13  (SELECT name_a, SUM(instr(col_def_a, col_def_a)) colmtch_a
     14  FROM taba
     15  WHERE instr(col_def_a, col_def_a) > 0
     16  GROUP BY name_a) x,
     17  (SELECT name_b, SUM(instr(col_def_a, col_def_b)) colmtch_b
     18  FROM taba, tabb
     19  WHERE instr(col_def_a, col_def_b) > 0
     20  GROUP BY name_b) y
     21  WHERE colmtch_a = colmtch_b;
    Enter VALUE FOR 1: yazoo
    Enter VALUE FOR 2: darku_orp

    no ROWS selected

    SQL>