Tuesday, May 17, 2011

Truncating empty temporary tables in Oracle for PeopleSoft applications

Truncating temporary tables is an effective way of resetting the high-watermark level in Oracle databases.

The following script just truncates those tables that are empty, as sometimes temporary tables containing data should not be deleted as the data may belong to processes in error.

   sqlstatement    VARCHAR2 (100);
   fulltablename   VARCHAR2 (40);
   t_count         NUMBER;

   CURSOR c1
      SELECT owner || '.' || table_name
        FROM all_tables a
                SELECT 'x'
                  FROM psrecdefn b
                 WHERE b.rectype = 7
                   AND a.table_name LIKE 'PS_' || b.recname || '%');
   OPEN c1;

      FETCH c1
       INTO fulltablename;


      sqlstatement :=
            'select count(*) from dual where exists (select NULL from '
         || fulltablename
         || ')';

      EXECUTE IMMEDIATE sqlstatement
                   INTO t_count;

      IF t_count = 1
         DBMS_OUTPUT.put_line ('WARNING: ' || fulltablename || ' has data, so it will not be truncated.');
         sqlstatement := 'truncate table ' || fulltablename;
         EXECUTE IMMEDIATE sqlstatement;

         DBMS_OUTPUT.put_line (fulltablename || ' was truncated');
      END IF;

Note: the script should be run when no process is running, as it is not blocking the tables after checking if they are empty. So, between the check and the truncate, someone may insert data. In any case, changing the script to lock the table should not be difficult.

Thursday, March 17, 2011

PeopleSoft Test Framework

PeopleTools 8.51 has introduced a new and exciting feature: PeopleSoft Test Framework (from now on PTF). This tool can be used to automate testing of PeopleSoft applications. Unlike other testing automation tools, PTF has the following advantages:

  • PTF is a testing automation tool designed to interact with PeopleSoft. It is quite easy to install and put it up to speed.
  • Tests are stored in PeopleSoft database as metadata. This allows developers to migrate tests between environments, pretty much like any other PeopleTools object.
  • Based on Usage Monitor, testers can determine which tests are impacted by a given application change: upgrade, bundle and/or customization.
PTF seems the perfect tool to automate a large degree of regression testing, thus reducing significantly the effort (and therefore cost) of testing after an application change. Particularly in upgrades, when test cycles are conducted over an over again, PTF may help to reduce the time and increase the quality needed for testing efforts.

There are still some shortfalls of PeopleSoft Test Framework. First of all, tests for PeopleSoft applications are still not delivered in the standard product, although some companies are already offering test bundles for certain modules (we at BNB are looking at offering a bundled service based on the test cases built on our installed customer base).

The second shortfall is the lack of stress testing capabilities. Still, there are plenty of tools on the market to perform this kind of testing.

All in all, it seems a very important step forward in PeopleSoft technology in order to reduce implementation and upgrade times and increase the ROI of those projects.