Enhancement request: Test Manager results

[bcoulam]

TestManager is so sweet, being able to easily regression test whole suites of .tst test files.

Unfortunately, at my last place of employment, most of the routines were oriented at returning ref cursors to the UI layer.

And at my current place of employment, the result of most routines is altered data in tables.

Would be a huge effort, but improving Test Manager to be able to compare the results in ref cursors, tables, and multi-line DBMS_OUTPUT would make Test Manager complete (at least for me).

A guy can dream...

 

[Marco Kalter]

It would be an effort, but it is doable of course. I have added this to the list of enhancement requests.

 

[ScottMattes]

in the mean time you could create a table that matches your application table, but with an extra column or two where you store at least an indicator of BEFORE and AFTER. Then, in your .tst you populate the table with BEFORE images, run the test and populate with AFTER images. You can then do SQL to look for changes.

 

[mike]

bcoulam - I've been dreaming of those things as well. It would also be nice if it created our test scripts for us. This is kind of long, so I hope it's clear enough.

My solution was to write a test package to do these things. The test package first makes all private procedures public and then recompile the package I'm testing. It will then automatically create test scripts for all public procedures. I then ftp the test scripts from the UNIX server to my C: drive.

To compare tables, the test_package also has procedures drop_table and copy_table. It has a function called compare_tables. The copy_table will call drop_table first to clear it out. The compare_tables will compare all columns except ones I pass in as a parameter as well as the columns like creation_date. It returns a count of the differences. It also dbms_outputs a query to run to show the differences.

I created templates in PL/SQL Developer that will call these routines. When I need to test code that will compare tables, I modify the test script using the templates. I add a :l_count parameter to check the counts. The finished test script will copy the table, use SQL to modify the copied table to how it should look, run the procedure/function being tested and then compare the tables. If the test fails, I press the green check icon, copy the SQL from the dbms_output and run that query to see the differences.

I didn't take much time to write this package, so it's not pretty. Here is drop_table, copy_table and compare_table from the test_package to give you an idea if you like. Ignore any of the "zfnd" stuff. Maybe you can take this and improve it to make it work for you?

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
   -- Procedure:   DROP_TABLE
   --
   -- Description: Drops a table.
   --
   -- Called by: PUBLIC
   -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

   PROCEDURE DROP_TABLE(i_table_name IN VARCHAR2) IS

   BEGIN
      EXECUTE IMMEDIATE 'drop table ' || i_table_name;
   EXCEPTION

      WHEN OTHERS THEN
         NULL; -- I know - I'm a hypocrite here.

   END DROP_TABLE;

   -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
   -- Procedure:   COPY_TABLE
   --
   -- Description:  This will copy the structure of a table and optionally the data.
   --
   -- Called by:  PUBLIC
   -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

   PROCEDURE COPY_TABLE(i_table_name        IN VARCHAR2
                       ,i_copied_table_name IN VARCHAR2 DEFAULT NULL
                       ,i_where_clause      IN VARCHAR2 DEFAULT 'WHERE 1 = 2') IS

--      PRAGMA AUTONOMOUS_TRANSACTION;

      l_copied_table_name all_tables.table_name%TYPE;
   BEGIN

      IF i_copied_table_name IS NOT NULL
      THEN
         l_copied_table_name := upper(i_copied_table_name);
      ELSE
         l_copied_table_name := substr(upper(i_table_name), 1, 26) || '_TST';
      END IF;

      zfnd_test_util.drop_table(i_table_name => l_copied_table_name);

      EXECUTE IMMEDIATE 'CREATE TABLE ' || l_copied_table_name || ' AS
                     (SELECT *
                      FROM   ' || i_table_name || '
                      ' || i_where_clause || ')';

--      COMMIT;

   EXCEPTION

      WHEN OTHERS THEN
         zfnd_debug.log('EXITING CREATE_TEST_TABLE AS EXCEPTION', 'N', 'ZFND_TEST_UTIL.CREATE_TEST_TABLE');
         dbms_output.put_line('+---------------------------------------------------------------------------+');
         dbms_output.put_line('ERROR IN ZFND_TEST_UTIL.CREATE_TEST_TABLE');
         dbms_output.put_line('ERROR: ' || SQLCODE || ' ' || SQLERRM);
         dbms_output.put_line('i_test_table: ' || i_copied_table_name);
         dbms_output.put_line('i_template_table: ' || i_table_name);
         dbms_output.put_line('+---------------------------------------------------------------------------+');
         RAISE_APPLICATION_ERROR(-20010, 'Error in ZFND_TEST_UTIL.CREATE_TEST_TABLE');

   END COPY_TABLE;

   -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
   -- Function:     COMPARE_TABLES
   --
   -- Description:  Compares 2 tables with the same column names. It will omit
   --               comparing the "who" columns since they will usually be different.
   --
   --               It returns the number of differences. It returns 0 if they
   --               are the same.
   --
   -- Called by:    PUBLIC
   -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

   FUNCTION COMPARE_TABLES(i_test_table                 IN VARCHAR2
                          ,i_test_table_where_clause    IN VARCHAR2 DEFAULT 'WHERE 1 = 1'
                          ,i_compare_table              IN VARCHAR2
                          ,i_compare_table_where_clause IN VARCHAR2 DEFAULT 'WHERE 1 = 1'
                          ,i_col_1_to_omit              IN VARCHAR2 DEFAULT NULL
                          ,i_col_2_to_omit              IN VARCHAR2 DEFAULT NULL
                          ,i_col_3_to_omit              IN VARCHAR2 DEFAULT NULL
                          ,i_col_4_to_omit              IN VARCHAR2 DEFAULT NULL
                          ,i_col_5_to_omit              IN VARCHAR2 DEFAULT NULL) RETURN INTEGER

    IS
      CURSOR c_columns(c_test_table all_tab_columns.table_name%TYPE) IS
         SELECT atc.column_name
         FROM   all_tab_columns atc
         WHERE  atc.table_name = upper(c_test_table)
         ORDER  BY atc.column_id;

      l_differences  INTEGER;
      l_differences2 INTEGER;

      l_column_list VARCHAR2(32000);
      l_query_text  VARCHAR2(32000);

      l_diff_data    SYS_REFCURSOR;
      l_first_column BOOLEAN := TRUE;
   BEGIN

      FOR c_columns_rec IN c_columns(i_test_table)
      LOOP
         IF upper(c_columns_rec.column_name) NOT IN
            ('LAST_UPDATE_DATE'
            ,'LAST_UPDATED_BY'
            ,'LAST_UPDATE_LOGIN'
            ,'CREATED_BY'
            ,'CREATION_DATE'
            ,nvl(i_col_1_to_omit, 'CREATION_DATE')
            ,nvl(i_col_2_to_omit, 'CREATION_DATE')
            ,nvl(i_col_3_to_omit, 'CREATION_DATE')
            ,nvl(i_col_4_to_omit, 'CREATION_DATE')
            ,nvl(i_col_5_to_omit, 'CREATION_DATE'))
         THEN
            IF l_first_column
            THEN
               l_first_column := FALSE;
               l_column_list  := 'SELECT ' || c_columns_rec.column_name || fnd_global.newline;
            ELSE
               l_column_list := l_column_list || ' ,' || c_columns_rec.column_name || fnd_global.newline;
            END IF;
         END IF;
      END LOOP c_columns;

      l_query_text := l_column_list || ' FROM ' || i_test_table || ' ' || fnd_global.newline || i_test_table_where_clause ||
                      fnd_global.newline || ' MINUS ' || fnd_global.newline || l_column_list || ' FROM ' || i_compare_table || ' ' ||
                      fnd_global.Newline || i_compare_table_where_clause;

      OPEN l_diff_data FOR 'SELECT COUNT(*) FROM (' || l_query_text || ')';
      FETCH l_diff_data
         INTO l_differences;
      CLOSE l_diff_data;

      IF l_differences > 0
      THEN
         dbms_output.put_line('-- Differences: ' || l_differences);
         dbms_output.put_line(l_query_text);
      END IF;

      l_query_text := l_column_list || ' FROM ' || i_compare_table || ' ' || fnd_global.newline || i_compare_table_where_clause ||
                      fnd_global.newline || ' MINUS ' || fnd_global.newline || l_column_list || ' FROM ' || i_test_table || ' ' ||
                      fnd_global.Newline || i_test_table_where_clause;

      OPEN l_diff_data FOR 'SELECT COUNT(*) FROM (' || l_query_text || ')';
      FETCH l_diff_data
         INTO l_differences2;
      CLOSE l_diff_data;

      IF l_differences2 > 0
      THEN
         dbms_output.put_line('-- Differences: ' || l_differences2);
         dbms_output.put_line(l_query_text);
      END IF;

      l_differences := nvl(l_differences, 0) + nvl(l_differences2, 0);

      RETURN l_differences;
   EXCEPTION

      WHEN OTHERS THEN
         zfnd_debug.log('EXITING COMPARE_TABLES AS EXCEPTION', 'N', 'ZFND_TEST_UTIL.COMPARE_TABLES');
         dbms_output.put_line('+---------------------------------------------------------------------------+');
         dbms_output.put_line('ERROR IN ZFND_TEST_UTIL.COMPARE_TABLES');
         dbms_output.put_line('ERROR: ' || SQLCODE || ' ' || SQLERRM);
         dbms_output.put_line('i_test_table: ' || i_test_table);
         dbms_output.put_line('i_test_table_where_clause: ' || i_test_table_where_clause);
         dbms_output.put_line('i_compare_table: ' || i_compare_table);
         dbms_output.put_line('i_compare_table_where_clause: ' || i_compare_table_where_clause);
         dbms_output.put_line('+---------------------------------------------------------------------------+');
         RAISE_APPLICATION_ERROR(-20010, 'Error in ZFND_TEST_UTIL.COMPARE_TABLES');

   END COMPARE_TABLES;

 

[ScottMattes]

or, you could use the 'as of timestamp'

select *
from your_table
minus
select *
from your_table as of timestamp( sysdate - 1 )

This works for sure on 10g, I think that it works on 9i.

 

[Gustavo]

I think this will require the Flashback DB option to be enabled.

 

[ScottMattes]

I can't be 100% sure, and the DBA who would know has left for the day, but I don't think so.

If you do the query, on any table, if the support isn't there you should get an error.

 

[Gustavo]

I thought it would be listed in v$option, but I couldn't find a line for Flashback here (9i). Can you check in 10g?

 

[ScottMattes]

In v$option of 10g I see:

Flashback Database TRUE
Flashback Table TRUE

Have you tried the 'from tablename as of timestamp( sysdate - 1 )' type query? We are debating whether anyone here still remembers how to log into our 9i system or I'd try it.

 

[ScottMattes]

also, go to this url "http://www.oracle.com/technology/deploy/availability/pdf/FlashbackQueryBWP.pdf" and search on "as of command"

 

[Gustavo]

Oops! You're right. I had gotten an error, and I assumed it was a missing-option issue (we don't have flashback), but the error is not related to this.

 

[ScottMattes]

And, now that I think of it, none of the tables that I tried it on are Flashback enabled, so this is the barebones flashback that comes with installing the RDBMS.