This discussion is archived
11 Replies Latest reply: Nov 30, 2012 10:43 AM by 977256 RSS

need a procedure to make minus query as output for 2 tables

977256 Newbie
Currently Being Moderated
Can anybody help me to create a procedure so that I could get minus query of 2 table as a result.

Requirement:

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)

minus

select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

Can any body give or help me to create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

Edited by: 974253 on Nov 30, 2012 5:39 AM
  • 1. Re: need a procedure to make minus query as output for 2 tables
    6363 Guru
    Currently Being Moderated
    You will need to use dynamic SQL

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011

    Although for 200 tables I think copy paste of static SQL would be faster to develop and will run more reliably.
  • 2. Re: need a procedure to make minus query as output for 2 tables
    myOra_help Journeyer
    Currently Being Moderated
    Do you have same data in both tables? Or what is the relation between them? Also how you decide which table from table1 to MINUS from which table from table 2.
  • 3. Re: need a procedure to make minus query as output for 2 tables
    ranit B Expert
    Currently Being Moderated
    Try using NOT EXISTS in place of MINUS.

    From docs -- http://docs.oracle.com/html/A86647_01/vmqtune.htm
    >
    MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. Rewriting queries using NOT EXISTS or NOT IN can enable them to take advantage of indexes, reducing the number of full table scans a clause may require.

    If the statement is re-written using NOT EXISTS, Oracle can use nested index scans in the subquery for rows in the primary statement.
  • 4. Re: need a procedure to make minus query as output for 2 tables
    977256 Newbie
    Currently Being Moderated
    src_table_list and tgt_table_list is just carrying table list which are present in database..

    So I need to pick up table for query generation with below clause..

    WHERE src_table_list.serial_no = tgt_table_list.serial_no
  • 5. Re: need a procedure to make minus query as output for 2 tables
    977256 Newbie
    Currently Being Moderated
    @ 3360      

    I can create manualy minus queries for 200 tables. but ...its just a phase1 count ..overall i'll have to write queries for more than 1200 tables...in my testing cycle.. :(
  • 6. Re: need a procedure to make minus query as output for 2 tables
    6363 Guru
    Currently Being Moderated
    ranit B wrote:
    Try using NOT EXISTS in place of MINUS.

    From docs -- http://docs.oracle.com/html/A86647_01/vmqtune.htm
    >
    MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. Rewriting queries using NOT EXISTS or NOT IN can enable them to take advantage of indexes, reducing the number of full table scans a clause may require.
    >
    Since the requirements is to compare all columns and rows in the tables full table scans will be the fastest way to do that. Indexes will not help here and the resulting code will be much more complex.
  • 7. Re: need a procedure to make minus query as output for 2 tables
    6363 Guru
    Currently Being Moderated
    974253 wrote:
    @ 3360      

    I can create manualy minus queries for 200 tables. but ...its just a phase1 count ..overall i'll have to write queries for more than 1200 tables...in my testing cycle.. :(
    Well it will only take six times longer. Which I still think will be faster than trying to create one single code block that will handle all tables and process the output. What do you intend to do with the results from the queries from all these different tables when they do not match? If the goal of this is validation it would seem that introducing a high level of unreliable automation in the process would be counter productive.
  • 8. Re: need a procedure to make minus query as output for 2 tables
    977256 Newbie
    Currently Being Moderated
    Actually it is a sudden requirement ..earlier validation was not in our scope..but now.. it is ...we have very short time window...
    so in that we need to write minus query manually. and then execeute manually...and it'd take more time ...thats why I was planning to make all minus queries through procedure...it'll save time
  • 9. Re: need a procedure to make minus query as output for 2 tables
    6363 Guru
    Currently Being Moderated
    974253 wrote:
    Actually it is a sudden requirement ..earlier validation was not in our scope..but now.. it is ...we have very short time window...
    so in that we need to write minus query manually. and then execeute manually...and it'd take more time ...thats why I was planning to make all minus queries through procedure...it'll save time
    As I said, I don't think it will save time, and I tend to consider unreliable validation as effective as no validation at all or worse, but it's your project so whatever works for you.
  • 10. Re: need a procedure to make minus query as output for 2 tables
    avish16 Explorer
    Currently Being Moderated
    select 'select '||chr(39)||src_table_list.tblname||chr(39)||','||chr(39)||trg_table_list.tblname||chr(39)||',count(*) from '||' ( select * from '||src_table_list.tblname||'minus select * from '||trg_table_list.tblname||');'
    from src_table_list, trg_table_list
    WHERE src_table_list.serial_no = tgt_table_list.serial_no

    The above statement should give output similar to below code and will list down all the table names in the 2 tables(i.e. above src and trg) -

    select 'src_table_list.tbl1','trg_table_list.tbl1',count(*) from
    (select col1, col2 from src_table_list.tbl1 minus select col1, col2 from trg_table_list.tbl1 );
    select 'src_table_list.tbl2','trg_table_list.tbl2',count(*) from
    (select col1, col2 from src_table_list.tbl2 minus select col1, col2 from trg_table_list.tbl2 );
    .....


    Now atleast you can run these statements as script and get to know what all tables are having count differences.
    I might have missed out on some syntax part in above code but hope to have helped you in some way as you will be specific with number of tables to check for differences.
  • 11. Re: need a procedure to make minus query as output for 2 tables
    977256 Newbie
    Currently Being Moderated
    Hey Avish many thanks...its a great help...so far!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points