how to validate the table structure

in Oracle Database; hi all, i have a scenario that i need to insert a data from one table to other which having ...
+ Post Reply + Post New Topic
Results 1 to 2 of 2
  1. #1
    karthikeyanc2003 is offline Junior Member
    Points: 750, Level: 14
    Join Date
    06 Aug 2009
    Posts
    24
    Points
    750

    how to validate the table structure

    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...
    Last edited by karthikeyanc2003; 08-12-2009 at 10:00 AM.


  2. #2
    zargon's Avatar
    zargon is offline Forum Guru
    Points: 26,230, Level: 96
    Join Date
    22 Oct 2008
    Location
    Aurora, CO
    Posts
    2,014
    Points
    26,230

    Re: how to validate the table structure

    Code sql:
     
    Quote Originally Posted by karthikeyanc2003 View Post
    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...
    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>

Other Solutions
  1. structure your code
    By ali0482 in forum Interview and Jobs Discussions
    Replies: 1
    Last Post: 08-18-2010, 05:28 AM
  2. 11i & R12 Table Structure
    By RIAZ in forum Oracle Financials
    Replies: 3
    Last Post: 03-21-2010, 07:54 AM
  3. Problem with when-validate-item trigger
    By Nincy666 in forum Oracle Forms and Reports
    Replies: 3
    Last Post: 05-07-2009, 06:50 AM
  4. View all table structure in schema
    By professional in forum SQL PL/SQL
    Replies: 6
    Last Post: 12-17-2008, 05:53 AM