Home » SQL & PL/SQL » SQL & PL/SQL » vaidating the table structure of 2 tables (merged)
vaidating the table structure of 2 tables (merged) [message #417958] Wed, 12 August 2009 05:09 Go to next message
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 #417961 is a reply to message #417958] Wed, 12 August 2009 05:15 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check user/all/dba_tab_columns.

By
Vamsi
Re: vaidating the table structure of 2 tables [message #417962 is a reply to message #417958] Wed, 12 August 2009 05:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #417972 is a reply to message #417958] Wed, 12 August 2009 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Why do you care if the column order is different?
2) There's a column_id column for that.
3) You really ought to know that the table structures are the same without having to write code to work it out.
Re: vaidating the table structure of 2 tables (merged) [message #417977 is a reply to message #417958] Wed, 12 August 2009 05:53 Go to previous messageGo to next message
karthikeyanc2003
Messages: 33
Registered: August 2009
Location: india
Member
Hi cook ,
Thanks for you suggestion it solve that problem , but the real problem that user needed is if the structure is not same we need give a message that these column is not found and data length is not same etc...
Re: vaidating the table structure of 2 tables (merged) [message #417978 is a reply to message #417965] Wed, 12 August 2009 05:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
something like
select 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 Go to previous messageGo to next message
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
Re: vaidating the table structure of 2 tables (merged) [message #417985 is a reply to message #417983] Wed, 12 August 2009 06:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who is "u"?
Please read OraFAQ Forum Guide and what is said about IM speak.
Also read what is said about formatting a post.

Regards
Michel

[Updated on: Wed, 12 August 2009 06:23]

Report message to a moderator

Previous Topic: Using source index in Merge Statement
Next Topic: FOR LOOP statement - Pass parameter from a recordset
Goto Forum:
  


Current Time: Sun Dec 04 14:35:48 CST 2016

Total time taken to generate the page: 0.06808 seconds