11 Replies Latest reply: Nov 30, 2012 12:43 PM by 977256 RSS

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

    977256
      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
          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
            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
              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
                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
                  @ 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
                    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
                      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
                        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
                          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
                            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
                              Hey Avish many thanks...its a great help...so far!!