1. 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,344
    Likes Received:
    347
    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>