Home » SQL & PL/SQL » SQL & PL/SQL » table data verification (sql*plus)
table data verification [message #354189] Thu, 16 October 2008 22:08 Go to next message
pravallika
Messages: 2
Registered: October 2008
Junior Member
I have two tables like tableA and tableB in different schemas
and they mapped to move all the data from tableA to tableB in these tables column names also different

for example
tableA column_name tableB column_name
tableA Acolumn1 tableB Bcolumn1
tableA Acolumn2 tableB Bcolumn2
tableA Acolumn3 tableB Bcolumn3

so all the data which is exists in tableA has moved to tableB

now I have to verify all the data moved correctly or not

for this i can check like this

select Bcolumn1 from tableB
minus
select Acolumn1 from tableA

in this case I have to check column by column but I have somany tables like this,
so is there any way to automate this process,


Re: table data verification [message #354204 is a reply to message #354189] Fri, 17 October 2008 00:06 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hint:-

Search through user_tab_columns and use some dynamic sql.
Use some PL/SQL.

Regards,
Rajat

Re: table data verification [message #354212 is a reply to message #354204] Fri, 17 October 2008 01:07 Go to previous messageGo to next message
pravallika
Messages: 2
Registered: October 2008
Junior Member
Hi Can u tell me little more how to do for all tables not only one table

Thanks for your help.
Re: table data verification [message #354219 is a reply to message #354212] Fri, 17 October 2008 01:23 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry we don't do someone's homework here. Shocked

We can point you to right direction what i already have done.

Search for Dynamic SQL.

Try some procedures in PL/SQL.

What about the table (user_tab_columns)??
Have you checked it out.

Try it you will find the solution.I won't post the complete
code here.Show us what you have tried.Then we are ready to help.
You know posting complete solution is against the ./fa/1707/0/ unless it is typical problem.

Regards,
Rajat


Re: table data verification [message #354220 is a reply to message #354219] Fri, 17 October 2008 01:29 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Quote:

for this i can check like this

select Bcolumn1 from tableB
minus
select Acolumn1 from tableA


I don't think, that this concept finds out, if all data are moved. Here an example, only one row of two is moved, the MINUS operator gives no result.

(
  select 'ABC' Bcolumn1 from dual
)  
MINUS
(
  select 'ABC' Acolumn1 from dual
UNION ALL
  select 'ABC'          from dual
)

Re: table data verification [message #354230 is a reply to message #354220] Fri, 17 October 2008 02:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you had no exceptiopn handling in the code that did the migration, and that no exceptions were raised, then every row of data that you processed will have been transfered.

You can check that every row from Table_A has a corresponding row in Table_B like this:
SELECT AColumn1,Acolumn2,AColumn3
FROM   table_A
minus
SELECT BColumn1,Bcolumn2,BColumn3
FROM   table_B


Previous Topic: restrict user for input
Next Topic: Maximum Size of LONG datatype in PL/SQL
Goto Forum:
  


Current Time: Sun Dec 11 05:59:18 CST 2016

Total time taken to generate the page: 0.15574 seconds