| vaidating the table structure of 2 tables (merged) [message #417958] |
Wed, 12 August 2009 05:09  |
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
|
Member |
|
|
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,
let me know how to achive this...
|
|
|
|
|
|
| Re: vaidating the table structure of 2 tables [message #417962 is a reply to message #417958] |
Wed, 12 August 2009 05:19  |
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
|
Junior Member |
|
|
Hi karthikey
The desired inforamtion about columns is available in the data dictionary all_tab_columns / all_tab_cols.
I would write pl/sql to first compare the count of columns in the 2 tables if matched then
i would loop through the source table finding matches in the destination table.
|
|
|
|
| Re: vaidating the table structure of 2 tables (merged) [message #417965 is a reply to message #417958] |
Wed, 12 August 2009 05:29 |
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
|
Member |
|
|
hi nithesh,
i did the same as u said. as below
SELECT Count(*) INTO ln_count1 FROM all_tab_columns WHERE table_name = Upper(ps_tablename) AND owner = Upper(inschema) ;
SELECT Count(*) INTO ln_count2 FROM all_tab_columns WHERE table_name = Upper(outtable)AND owner = Upper(outschema) ;
SELECT Count(*) INTO ln_count3 FROM all_tab_columns a , all_tab_columns b WHERE a.table_name = Upper(ps_tablename)
AND b.table_name = Upper(outtable) AND a.COLUMN_name = b.column_name AND a.data_type = b.data_type
AND a.owner = Upper(inschema) AND b.owner = Upper(outschema)
AND a.data_length <= b.data_length;
i compare ln_count1,ln_count2,ln_count3
but i am not able to get in case of the order of a column in the table is different
[Updated on: Wed, 12 August 2009 05:43] Report message to a moderator |
|
|
|
|
|
|
|
| Re: vaidating the table structure of 2 tables (merged) [message #417978 is a reply to message #417965] |
Wed, 12 August 2009 05:54 |
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
|
Junior Member |
|
|
my suggestion was
1) check if the mumbe rof columns in the 2 tables is different
2) then create a cursor in which you select all relevant columns in all_tab_cols : namely column_id, column name, datetype ... anything else u wish to compare
3) open this cursor in 2) in a cursor for loop for the source table searching for matches in the destination table columns (possibly using another instance of the same cursor).
4) if all matches for all columns , ure ok to insert else u raise an exception and exit.
Offcourse this is just a possible recomendation ,
you an also try to do this is a different way.
Possibly use the MINUS operator on a query on all_tab_cols to find the difference for the source and destination tables ...
|
|
|
|
| Re: vaidating the table structure of 2 tables (merged) [message #417982 is a reply to message #417965] |
Wed, 12 August 2009 05:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
something likeselect count(*)
from all_tab_columns a, all_tab_columns b
where a.column_name = b.column_name
and a.data_type = b.data_type
and a.column_id = b.column_id
and...
You'll need to write some slightly cunning sql statements to cope with the data_precision and data_scale columns only being populated for some data types.
At the end, this query should return the number of rows in the table. If it doesn't, then your table structures don't match.
|
|
|
|
| Re: vaidating the table structure of 2 tables (merged) [message #417983 is a reply to message #417982] |
Wed, 12 August 2009 06:02 |
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
|
Junior Member |
|
|
Try this ,,,
select a.COLUMN_ID ||','|| a.COLUMN_NAME || ',' || a.DATA_TYPE as str
into v_str1
from all_tab_cols a
where a.TABLE_NAME = 'TAB1'
order by column_id;
select a.COLUMN_ID ||','|| a.COLUMN_NAME || ',' || a.DATA_TYPE as str
into v_str2
from all_tab_cols a
where a.TABLE_NAME = 'TAB2'
order by column_id;
if v_str1 = v_str2 then
insert ...
else
raise e_1;
end if;
here u can add any other parameter u wish to compare in the str variables ..
cheers
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
