Table Encapsulation Package for UT_TESTCASE

Overview

Tips and Usage Notes

The Fine Print

Data Structure Types

Cursors

Validation Programs

Retrieval Programs

Row Count Functions

Insert Programs

Update Programs

Delete Programs

Overview

This document provides information on how to use the table encapsulator package named te_ut_testcase. This package allows you to perform all SQL-related actions against the table, UT_TESTCASE, through a PL/SQL interface or API (application programmatic interface).

Instead of writing an UPDATE SQL statement in your program, for example, you would call the te_ut_testcase.upd procedure. Instead of writing a SELECT statement to retrieve a row from UT_TESTCASE for a specific primary key, you would call the te_ut_testcase.onerow function. Why bother with these programs? Why not simply access the table directly? By using the table encapsulator package, you will achieve a higher level of performance in your application, it will be easier to maintain your code, and you will be able to write your code faster.

The documentation for the API is broken out into the following categories:

Return to top

Tips and Usage Notes

1. Referencing Elements of Package: Unless otherwise noted, whenever you use a code element defined in te_ut_testcase, you must preface that element with "te_ut_testcase.", as in:

te_ut_testcase.ins (...);

2. Avoiding System-Assigned Database Object Names: You may encounter package elements with names like "SYS_C00642". This will occur when table constraints (including primary keys, foreign keys and check constraints) are not given names at the time they are created. Instead, Oracle assigns a name using a sequential number. Obviously, this is not very readable code. There are two ways to get around these undesirable names: ask the DBA to provide informative names along with the constraint creation statements or use the PL/Generator aliasing feature (Coding Standards screen) to provide names for these constraints which will impact only the generated code.

The first approach, changing the name of the constraint in the database, is the recommended approach.

3. Choosing Between Overloaded Programs: Many of the programs in this package are overloaded. That means that there are more than one program with the same name. In most cases, the difference between these overloadings is that one program relies on passing individual, scalar values as arguments, while others allow you to pass record structures.

Whenever possible, you should rely on parameter lists that pass records, instead of individual values. It is quite likely that your table structure will change over time, and with the table encapsulator package. If you always work with records, you will keep to an absolute minimum the number of programs already relying on the package's API which have to be fixed.

Return to top

The Fine Print

This document was generated using Quest Software's PL/Generator (www.Quest Software.com) version PRO-2000.2.8.

Generation date/time: May 09, 2003 16:37:47

Return to top

Data Structure Types

This section documents the PL/SQL TYPE statements declaring data structures (mostly record TYPES) which you will use to declare records in your programs.

The te_ut_testcase.pky_rt record TYPE defines a record structure for the primary key of UT_TESTCASE. This record TYPE is used in various programs in the package, including isnullpky and del.

   TYPE te_ut_testcase.pky_rt IS RECORD (
      ID UT_TESTCASE.ID%TYPE,
      found BOOLEAN,
      notfound BOOLEAN
      );

Here is an example of using the primary key record TYPE:

DECLARE
   pky_rec te_ut_testcase.pky_rt;
BEGIN
   pky_rec.ID := v_ID; /* set in outer block */

   te_ut_testcase.del (
      pky_rec,
      v_numrows);
END;
/

The te_ut_testcase.ut_testcase_idx1_rt record TYPE corresponds to the columns found in the ut_testcase_idx1 index for UT_TESTCASE. It is used by te_ut_testcase.ut_testcase_idx1$val function to return all the values of an index for a given primary key.

   TYPE te_ut_testcase.ut_testcase_idx1_rt IS RECORD (
      NAME UT_TESTCASE.NAME%TYPE
      );

Return to top

Cursors

This section documents the pre-defined cursors that retrieve rows of information from UT_TESTCASE. As a rule, you should never write your own cursors against UT_TESTCASE. Check instead the set of cursors already created for you in te_ut_testcase. If the query you need is not present, contact your program administrator to enhance the package. If you take this approach, you will improve the performance of your application by increasing the usage of pre-parsed cursors in the SGA. You will also make it much easier to maintain the application code as the underlying table changes.

The te_ut_testcase.allbypky_cur cursor returns the specified columns for all rows, ordered by the primary key. Here is the definition of that cursor:

   CURSOR te_ut_testcase.allbypky_cur
   IS
      SELECT *
        FROM UT_TESTCASE
       ORDER BY
         ID
      ;

The te_ut_testcase.allforpky_cur cursor returns the specified columns for a single row identified by the primary key. Here is the definition of that cursor:

   CURSOR te_ut_testcase.allforpky_cur (
      id_in IN UT_TESTCASE.ID%TYPE
      )
   IS
      SELECT *
        FROM UT_TESTCASE
       WHERE
         ID = id_in
      ;

The te_ut_testcase.ut_testcase_unitest_fk_all_cur cursor returns the specified columns for a all rows identified by the foreign key, ut_testcase_unitest_fk. Here is the definition of that cursor:

   CURSOR te_ut_testcase.ut_testcase_unitest_fk_all_cur (
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE
      )
   IS
      SELECT *
        FROM UT_TESTCASE
       WHERE
          UNITTEST_ID = unittest_id_in
          ;

Cursor Management

This package not only provides you with pre-built cursors, but also offers a set of open and close procedures to make it easier for you to work with the cursors. As you will see below, these programs offer some special features you are unlikely to code yourself, so you will be much better off relying on the te_ut_testcase open and close procedures instead of issuing explicit OPEN and CLOSE statements yourself.

Open Cursors

Each cursor defined in the package has a corresponding open procedure. Each of these procedures has a final close_if_open parameter. This parameter controls the behavior of the open request in cases when the cursor is already open (packaged cursors stay open until you close them explicitly. This is different behavior from locally declared cursors, which close automatically when the block in which they were declared terminates).

If you pass TRUE for this parameter (the default), then the procedure will close the cursor if it is already open. If you pass FALSE, then the request to open the cursor is ignored if the cursor is already open. Why would you pass FALSE for close_if_open? Your application might be architected so that a particular cursor stays open across multiple program calls, allowing you to fetch additional rows from any one of those programs. In this case, you will want to make sure the cursor is open before you fetch, but you will not want any of the programs to close the cursor prematurely.

The te_ut_testcase.open_allbypky_cur closes the te_ut_testcase.allbypky_cur cursor documented in the previous section.

   PROCEDURE te_ut_testcase.open_allbypky_cur (
      close_if_open IN BOOLEAN := TRUE
      );

The te_ut_testcase.open_allforpky_cur closes the te_ut_testcase.allforpky_cur cursor documented in the previous section.

PROCEDURE te_ut_testcase.open_allforpky_cur (
      id_in IN UT_TESTCASE.ID%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

The te_ut_testcase.open_ut_testcase_unitest_fk_al closes the te_ut_testcase.ut_testcase_unitest_fk_all_cur cursor documented in the previous section.

   PROCEDURE te_ut_testcase.open_ut_testcase_unitest_fk_al (
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

Close Cursors

Each cursor defined in the package has a corresponding close procedure. These procedures will only close their respective cursors if they are open at the time the procedures are called. In addition, there is a single procedure, te_ut_testcase.closeall, which closes any open cursors associated with this package.

The following procedure closes the te_ut_testcase.allforpky_cur cursor.

   PROCEDURE te_ut_testcase.close_allforpky_cur;

The following procedure closes the te_ut_testcase.allbypky_cur cursor.

   PROCEDURE te_ut_testcase.close_allbypky_cur;

The following procedure closes the te_ut_testcase.ut_testcase_unitest_fk_all_cur cursor.

   PROCEDURE te_ut_testcase.close_ut_testcase_unitest_fk_a;

The following procedure closes any open cursors that are associated with this package.

   PROCEDURE te_ut_testcase.closeall;

Return to top

Validation Programs

The programs in this section can be used to validate data associated with UT_TESTCASE. These validations may be used to confirm the presence of a primary key and check for record equality. If there arecheck constraints stored in the database for this table, you will also find functions that validate (mirror) those constraints for values you pass to it.

Check for Non-NULL Primary Key

Use the isnullpky function to check easily that all columns in the primary key are not NULL. You can use this function to validate that the primary key or the row returned by a call to a function is valid.

   FUNCTION te_ut_testcase.isnullpky (
      rec_in IN UT_TESTCASE%ROWTYPE
      )
   RETURN BOOLEAN;
   FUNCTION te_ut_testcase.isnullpky (
      rec_in IN pky_rt
      )
   RETURN BOOLEAN;

The following code illustrates how you can put these functions to use.

DECLARE
   v_row UT_TESTCASE%ROWTYPE;
BEGIN
   v_row := te_ut_testcase.onerow (
      v_ID
      );
   IF te_ut_testcase.isnullpky (v_row)
   THEN
      DBMS_OUTPUT.PUT_LINE ('Row not found for specified primary key.');
   ELSE
      process_data (v_row);
   END IF;
END;
/

Record Equality Checks

PL/SQL does not allow you to perform a record-level comparison of two records for equality. This package, however, contains two functions - overloaded as te_ut_testcase.recseq - which emulate record-level equality checks.

You can provide as an argument a records of the following type(s) to the te_ut_testcase.recseq function:

A record defined with UT_TESTCASE%ROWTYPE

The header for the table-record version is:

   FUNCTION recseq (rec1 IN UT_TESTCASE%ROWTYPE, rec2 IN UT_TESTCASE%ROWTYPE)
      RETURN BOOLEAN;

The header for the primary key-record version is:

   FUNCTION recseq (rec1 IN pky_rt, rec2 IN pky_rt) RETURN BOOLEAN;

Here is an example of using the recseq function to check equality between two records:

DECLARE
   rec1 UT_TESTCASE%ROWTYPE;
   rec2 UT_TESTCASE%ROWTYPE;
BEGIN
   rec1 := te_ut_testcase.onerow (
      v_ID
      );
   IF te_ut_testcase.recseq (rec1, rec2) /* Will return FALSE */
   THEN
      ...
   END IF;
END;
/

Return to top

Retrieval Programs

The package offers a set of functions that allow you to retrieve values from individual rows stored in UT_TESTCASE.

The following function retrieves a row of data (all columns) for the specified primary key.

   FUNCTION te_ut_testcase.onerow (
      id_in IN UT_TESTCASE.ID%TYPE
      )
      RETURN UT_TESTCASE%ROWTYPE;

The following function retrieves the primary key values in the form of a record for a single entry in the unique index identified by the function name.

   FUNCTION te_ut_testcase.ut_testcase_idx1$pky (
      name_in IN UT_TESTCASE.NAME%TYPE
      )
      RETURN pky_rt
      ;

To confirm that the index values retrieved a primary key successfully, pass the primary key record to the isnullpky and see if it returns TRUE.

The ut_testcase_idx1$val function returns the values for a single entry in the unique index for the specified primary key. It is, in other words, the inverse of the ut_testcase_idx1$pky function.

   FUNCTION te_ut_testcase.ut_testcase_idx1$val (
      id_in IN UT_TESTCASE.ID%TYPE
      )
   RETURN ut_testcase_idx1_rt;

The ut_testcase_idx1$row function retrieves all the columns for the row identified by the entry in the unique index specified by the parameter values.

   FUNCTION te_ut_testcase.ut_testcase_idx1$row (
      name_in IN UT_TESTCASE.NAME%TYPE
      )
   RETURN UT_TESTCASE%ROWTYPE;

Return to top

Row Count Functions

This API contains functions that return the number of rows (based on various criteria) in the underlying table.

Count of All Rows

The te_ut_testcase.rowcount function returns the count of all rows in the table. The header is:

   FUNCTION te_ut_testcase.rowcount RETURN INTEGER;

Here is an example of using this function:

BEGIN
   IF te_ut_testcase.rowcount = 0
   THEN
      DBMS_OUTPUT.PUT_LINE ('Table is empty!');
   END IF;
END;

Count of Rows for a Primary Key

The te_ut_testcase.pkyrowcount function returns the number of rows in the table for a given primary key (which may consist of one or more columns). The header for this function is:

   FUNCTION pkyrowcount (
      id_in IN UT_TESTCASE.ID%TYPE
      )
      RETURN INTEGER;

You can use this function to determine if a row exists in the table for a primary key. You can also determine if there is a data integrity issue -- this function should return 0 if the primary is not present and 1 if it is. If it returns a number greater than 1, you may have a problem. Here is an example of how you might use this function:

BEGIN
   IF te_ut_testcase.pkyrowcount (pkycolval1, ..., pkycolvalN) = 0
   THEN
      DBMS_OUTPUT.PUT_LINE ('No row defined for this primary key!');
   END IF;
END;

Count of Rows for Foreign Key UT_TESTCASE_UNITEST_FK

The te_ut_testcase.ut_testcase_unitest_fkrowcount function returns the number of rows in the table for a specified foreign key value set (one or more columns, as determined by the foreign key definition itself). The header for this function is:

   FUNCTION te_ut_testcase.ut_testcase_unitest_fkrowcount (
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE
      )
      RETURN INTEGER;

Return to top

Insert Programs

Instead of issuing INSERT statements directly in your code, you will call the te_ut_testcase.ins procedure. This procedure performs error handling and can even convert an INSERT into an UPDATE when it encounters a DUP_VAL_ON_INDEX error (if, in other words, the primary key is already present in the database).

Record Initialization

You can perform an insert by passing individual column values or by providing a table-based record. If you use a record, you may want to call a record initialization program to assign the default values for each column to the field in the record.

You can initialize a record with a function or a procedure as shown in the headers below:

   FUNCTION te_ut_testcase.initrec
      (allnull IN BOOLEAN := FALSE) RETURN UT_TESTCASE%ROWTYPE;

   PROCEDURE te_ut_testcase. initrec  (
      rec_inout IN OUT UT_TESTCASE%ROWTYPE,
      allnull IN BOOLEAN := FALSE);

If you pass TRUE for the allnull argument, then all the fields in the record will be set to NULL. Otherwise, the fields will be set to the default values for each column as specified in the table definition (NULL, unless overridden with another default value).

Insert Procedures

This package provides more than one procedure with which to insert a row of data.

Insert Providing Primary Key and Individual Fields

This version of ins requires that you pass not only the non-primary key column values, but also a previously-generated or acquired primary key value (or values, in the case of multiple primary key columns).

The upd_on_dup argument controls the behavior of ins when the attempt to INSERT raises a DUP_VAL_ON_INDEX error. This error occurs when a row already exists in the table with the same unique index column values as the row being inserted.

If you pass FALSE for this argument (the default), then ins passes the DUP_VAL_ON_INDEX exception back to the calling program (or sets the errnum argument if you are using return code exception handling).

If you pass TRUE for upd_on_dup, then if the INSERT fails with that error, the procedure will attempt to perform an update for the row identified by the primary key by calling upd.

So if the DUP_VAL_ON_INDEX error is caused by a non-primary key unique index, this feature will not operate properly.

   PROCEDURE te_ut_testcase.ins (
      id_in IN UT_TESTCASE.ID%TYPE,
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE DEFAULT NULL,
      name_in IN UT_TESTCASE.NAME%TYPE DEFAULT NULL,
      seq_in IN UT_TESTCASE.SEQ%TYPE DEFAULT 1,
      description_in IN UT_TESTCASE.DESCRIPTION%TYPE DEFAULT NULL,
      status_in IN UT_TESTCASE.STATUS%TYPE DEFAULT NULL,
      declarations_in IN UT_TESTCASE.DECLARATIONS%TYPE DEFAULT NULL,
      setup_in IN UT_TESTCASE.SETUP%TYPE DEFAULT NULL,
      teardown_in IN UT_TESTCASE.TEARDOWN%TYPE DEFAULT NULL,
      exceptions_in IN UT_TESTCASE.EXCEPTIONS%TYPE DEFAULT NULL,
      test_id_in IN UT_TESTCASE.TEST_ID%TYPE DEFAULT NULL,
      prefix_in IN UT_TESTCASE.PREFIX%TYPE DEFAULT NULL,
      assertion_in IN UT_TESTCASE.ASSERTION%TYPE DEFAULT NULL,
      inline_assertion_call_in IN UT_TESTCASE.INLINE_ASSERTION_CALL%TYPE DEFAULT 'N',
      executions_in IN UT_TESTCASE.EXECUTIONS%TYPE DEFAULT NULL,
      failures_in IN UT_TESTCASE.FAILURES%TYPE DEFAULT NULL,
      last_start_in IN UT_TESTCASE.LAST_START%TYPE DEFAULT NULL,
      last_end_in IN UT_TESTCASE.LAST_END%TYPE DEFAULT NULL,
      upd_on_dup IN BOOLEAN := FALSE
      );

Insert with Record, Providing Primary Key

In this version of ins you provide a table-based record (defined using UT_TESTCASE%ROWTYPE); all fields, including those corresponding to the primary key are used in the insert action. This ins procedure, in other words, does not generate a primary key. You must provide one all on your own.

If you want the insert to take advantage of default column values as found in the table definition, you should call te_ut_testcase.initrec or te_ut_testcase.initrec.

The upd_on_dup argument controls the behavior of ins when the attempt to INSERT raises a DUP_VAL_ON_INDEX error. This error occurs when a row already exists in the table with the same unique index column values as the row being inserted.

If you pass FALSE for this argument (the default), then ins passes the DUP_VAL_ON_INDEX exception back to the calling program (or sets the errnum argument if you are using return code exception handling).

If you pass TRUE for upd_on_dup, then if the INSERT fails with that error, the procedure will attempt to perform an update for the row identified by the primary key by calling upd.

So if the DUP_VAL_ON_INDEX error is caused by a non-primary key unique index, this feature will not operate properly.

   PROCEDURE te_ut_testcase.ins (rec_in IN UT_TESTCASE%ROWTYPE,
      upd_on_dup IN BOOLEAN := FALSE
      );

Return to top

Update Programs

Updating through the package API can be a bit more complex than performing an insert or a delete. This package provides a single upd procedure through which you can update all, some or just one column in a table's row for a specific primary key.

The way such a general update procedure works is as follows:

Impact on Triggers

Since the general update procedure will update all non-primary key columns in the table, regardless of whether or not the value has changed, you should make sure that all of your UPDATE triggers employ the WHEN clause. This clause will allow you to specify that processing of the trigger only takes place when the value is changed.

If you do not want to have to insert this code into your triggers, then you should use single-column update procedures (which can be generated as a part of this package). Contact your PL/Generator Designer to take this action).

Forcing NULL Values in Update

This package contains a PL/SQL record structure that keeps track of the update-if-NULL status of each column of the table. It is, in essence, a set of flags. By default, this record says to the update procedure: ignore this value if it is NULL. You can change individual flags in this record to say "set the column to NULL" or, in other words, "I really mean it. I want to make this column value NULL" by calling a force function.

Here are the headers of the force functions for each column:

   FUNCTION te_ut_testcase.unittest_id$frc
      (unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.UNITTEST_ID%TYPE;
   FUNCTION te_ut_testcase.name$frc
      (name_in IN UT_TESTCASE.NAME%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.NAME%TYPE;
   FUNCTION te_ut_testcase.seq$frc
      (seq_in IN UT_TESTCASE.SEQ%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.SEQ%TYPE;
   FUNCTION te_ut_testcase.description$frc
      (description_in IN UT_TESTCASE.DESCRIPTION%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.DESCRIPTION%TYPE;
   FUNCTION te_ut_testcase.status$frc
      (status_in IN UT_TESTCASE.STATUS%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.STATUS%TYPE;
   FUNCTION te_ut_testcase.declarations$frc
      (declarations_in IN UT_TESTCASE.DECLARATIONS%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.DECLARATIONS%TYPE;
   FUNCTION te_ut_testcase.setup$frc
      (setup_in IN UT_TESTCASE.SETUP%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.SETUP%TYPE;
   FUNCTION te_ut_testcase.teardown$frc
      (teardown_in IN UT_TESTCASE.TEARDOWN%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.TEARDOWN%TYPE;
   FUNCTION te_ut_testcase.exceptions$frc
      (exceptions_in IN UT_TESTCASE.EXCEPTIONS%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.EXCEPTIONS%TYPE;
   FUNCTION te_ut_testcase.test_id$frc
      (test_id_in IN UT_TESTCASE.TEST_ID%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.TEST_ID%TYPE;
   FUNCTION te_ut_testcase.prefix$frc
      (prefix_in IN UT_TESTCASE.PREFIX%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.PREFIX%TYPE;
   FUNCTION te_ut_testcase.assertion$frc
      (assertion_in IN UT_TESTCASE.ASSERTION%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.ASSERTION%TYPE;
   FUNCTION te_ut_testcase.inline_assertion_call$frc
      (inline_assertion_call_in IN UT_TESTCASE.INLINE_ASSERTION_CALL%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.INLINE_ASSERTION_CALL%TYPE;
   FUNCTION te_ut_testcase.executions$frc
      (executions_in IN UT_TESTCASE.EXECUTIONS%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.EXECUTIONS%TYPE;
   FUNCTION te_ut_testcase.failures$frc
      (failures_in IN UT_TESTCASE.FAILURES%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.FAILURES%TYPE;
   FUNCTION te_ut_testcase.last_start$frc
      (last_start_in IN UT_TESTCASE.LAST_START%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.LAST_START%TYPE;
   FUNCTION te_ut_testcase.last_end$frc
      (last_end_in IN UT_TESTCASE.LAST_END%TYPE DEFAULT NULL)
      RETURN UT_TESTCASE.LAST_END%TYPE;

When you call this function it does two things:

  1. Returns the value you pass into it, the default being NULL.
  2. Sets the force flag for that column to "Yes, update if NULL".

The consequence is that when you then call te_ut_testcase.upd and pass a NULL value for that column, its value will be set to NULL (and not ignored).

You can use these functions in one of two ways:

  1. Assignment statement: a "stand-alone" call to the function, returning the value to a PL/SQL variable of the appropriate type.
  2. In-line execution: call the function within a call to the upd procedure, forcing the update of that column to NULL. This will be the way that you most commonly deploy the force functions.

The following example shows the use of the force function to set every non-primary key column to NULL in the update. This will, of course, cause a failure in the update if any of the columns are defined to be NOT NULL.

DECLARE
   numrows INTEGER;
BEGIN
   te_ut_testcase.upd(
      v_ID, /* set in an outer block */
      unittest_id_in => te_ut_testcase.unittest_id$frc
      name_in => te_ut_testcase.name$frc
      seq_in => te_ut_testcase.seq$frc
      description_in => te_ut_testcase.description$frc
      status_in => te_ut_testcase.status$frc
      declarations_in => te_ut_testcase.declarations$frc
      setup_in => te_ut_testcase.setup$frc
      teardown_in => te_ut_testcase.teardown$frc
      exceptions_in => te_ut_testcase.exceptions$frc
      test_id_in => te_ut_testcase.test_id$frc
      prefix_in => te_ut_testcase.prefix$frc
      assertion_in => te_ut_testcase.assertion$frc
      inline_assertion_call_in => te_ut_testcase.inline_assertion_call$frc
      executions_in => te_ut_testcase.executions$frc
      failures_in => te_ut_testcase.failures$frc
      last_start_in => te_ut_testcase.last_start$frc
      last_end_in => te_ut_testcase.last_end$frc
      numrows
      );
END;
/

Resetting the Force Flags

You can reset all of the force flags to "don't update if NULL" by calling the following procedure:

PROCEDURE te_ut_testcase.reset$frc;

Note: this program is called by upd automatically if you pass TRUE (the default value) for the reset_in argument.

Update Any/All Column Procedures

You perform an update on one row at a time, identified by the primary key. You can pass the values of the columns you want to update either through individual parameters (one parameter per column) or through a record of type UT_TESTCASE%ROWTYPE.

Update by Primary Key, using Individual Fields

When you call upd, you must provide a value for each column in the primary key. You can then pass a value for each column you wish to update. The remaining arguments for upd are described after the header.

   PROCEDURE te_ut_testcase.upd (
      id_in IN UT_TESTCASE.ID%TYPE,
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE DEFAULT NULL,
      name_in IN UT_TESTCASE.NAME%TYPE DEFAULT NULL,
      seq_in IN UT_TESTCASE.SEQ%TYPE DEFAULT NULL,
      description_in IN UT_TESTCASE.DESCRIPTION%TYPE DEFAULT NULL,
      status_in IN UT_TESTCASE.STATUS%TYPE DEFAULT NULL,
      declarations_in IN UT_TESTCASE.DECLARATIONS%TYPE DEFAULT NULL,
      setup_in IN UT_TESTCASE.SETUP%TYPE DEFAULT NULL,
      teardown_in IN UT_TESTCASE.TEARDOWN%TYPE DEFAULT NULL,
      exceptions_in IN UT_TESTCASE.EXCEPTIONS%TYPE DEFAULT NULL,
      test_id_in IN UT_TESTCASE.TEST_ID%TYPE DEFAULT NULL,
      prefix_in IN UT_TESTCASE.PREFIX%TYPE DEFAULT NULL,
      assertion_in IN UT_TESTCASE.ASSERTION%TYPE DEFAULT NULL,
      inline_assertion_call_in IN UT_TESTCASE.INLINE_ASSERTION_CALL%TYPE DEFAULT NULL,
      executions_in IN UT_TESTCASE.EXECUTIONS%TYPE DEFAULT NULL,
      failures_in IN UT_TESTCASE.FAILURES%TYPE DEFAULT NULL,
      last_start_in IN UT_TESTCASE.LAST_START%TYPE DEFAULT NULL,
      last_end_in IN UT_TESTCASE.LAST_END%TYPE DEFAULT NULL,
      rowcount_out OUT INTEGER,
      reset_in IN BOOLEAN DEFAULT TRUE
      );

The rowcount_out argument contains the number of rows updated by your request.

The reset_in argument controls the resetting of the force record. If TRUE, the default, then the force record is reset to its initial values (meaning, a NULL value is ignored). If FALSE, then the force record is not reset. The same columns for which a NULL value was applied in the current UPDATE will be "marked" for update-if-NULL in the next call to te_ut_testcase.upd.

Update by Primary Key, using Record

This version of upd allows you to pass your column values for update through a record of type UT_TESTCASE%ROWTYPE. The fields corresponding to primary key columns must be filled. All other fields can be assigned values as is appropriate to the update you want to perform. The remaining arguments for upd are described after the header.

   PROCEDURE te_ut_testcase.upd (rec_in IN UT_TESTCASE%ROWTYPE,
      rowcount_out OUT INTEGER,
      reset_in IN BOOLEAN DEFAULT TRUE);

The rowcount_out argument contains the number of rows updated by your request. This argument should return either 0 or 1.

The reset_in argument controls the resetting of the force record. If TRUE, the default, then the force record is reset to its initial values (meaning, a NULL value is ignored). If FALSE, then the force record is not reset. The same columns for which a NULL value was applied in the current UPDATE will be "marked" for update-if-NULL in the next call to te_ut_testcase.upd.

Update Individual Columns

This package contains one or more procedures you can use to update individual columns for a specific primary key without having to call the general update procedure. You may want to do this to avoid the overhead of updating each column (even if it is updated to the same value) and initiating unnecessary trigger activity.

There are two versions of the single-column update for each specified column: one which accepts the primary key as individual columns and another which allows you to pass the primary key as a PL/SQL record.

Return to top

Delete Programs

Rather than issuing an explicit DELETE in your programs, you will call te_ut_testcase.del. You can delete the single row associated with a primary key, or you can delete all rows associated with a foreign key value.

This package does not perform any kind of special logic or processing for cascading deletes, foreign key dependencies, or other possible complications of delete operations.

Delete by Primary Key using Individual Fields

With this delete procedure, you provide the values of the primary key in individual arguments.

The rowcount_out returns the numbers of rows affected by the delete request (either 0 or 1, since it is a primary key-based operation).

   PROCEDURE te_ut_testcase.del (
      id_in IN UT_TESTCASE.ID%TYPE,
      rowcount_out OUT INTEGER);

Delete by Primary Key using Record

With this delete procedure, you provide the values of the primary key in a record of type te_ut_testcase.pky_rt.

The rowcount_out returns the numbers of rows affected by the delete request (either 0 or 1, since it is a primary key-based operation).

   PROCEDURE te_ut_testcase.del (rec_in IN pky_rt,
      rowcount_out OUT INTEGER);

You can also request a delete of a row by passing a record of type UT_TESTCASE%ROWTYPE, which contains more than just the primary key columns. Here is the header for this version of del:

   PROCEDURE del (rec_in IN UT_TESTCASE%ROWTYPE,
      rowcount_out OUT INTEGER);

Delete by Foreign Key UT_TESTCASE_UNITEST_FK

With this delete procedure, you delete all the rows in the table associated with a specific foreign key value. You provide the values of the different columns in the foreign as individual arguments.

The rowcount_out returns the numbers of rows affected by the delete request (this number is not restricted to 0 or 1, since it is a foreign key-based operation).

   PROCEDURE te_ut_testcase.delby_ut_testcase_unitest_fk (
      unittest_id_in IN UT_TESTCASE.UNITTEST_ID%TYPE,
      rowcount_out OUT INTEGER
      );

Return to top

Performance Enhancers

This package contains one or more features that you may find useful in enhancing the performance of your database access.

Pinning te_ut_testcase in Shared Memory

If you want to make sure that programs in this package are instantly available for execution, you can "pin" the package into shared memory. If you pin a package, it will not be a candidate for removal by the least-recently-used (LRU) algorithm employed by Oracle to manage its shared memory area.

You will usually pin programs when the database is first initialized. That offers the best guarantee that sufficient contiguous memory is found for your code.

There are two steps you will need to take to pin your package:

  1. Call DBMS_SHARED_POOL.KEEP to "register" your package as a pinnable package.
  2. Reference an element in te_ut_testcase so that the package will be loaded into memory.

Here is the code you will need to run to enable pinning of te_ut_testcase:

   DBMS_SHARED_POOL.KEEP ('schemaname.te_ut_testcase');

where schemaname is the name of the schema which owns te_ut_testcase.

Here is the code you will run after calling the KEEP built-in to load your package into memory:

te_ut_testcase.pinme;

Both of these lines of code should be placed in an initialization script that is run when the database is started. This is a job for your Database Administrator.

Return to top