Test case

A Test case is a sequence of repeatable steps that can be used to: (1) test or verify that an application is functioning as expected; or (2) demonstrate a bug or functionality that doesn't work. A test case should at least contain an input description, the test sequence, and a description of the expected behaviour.

Minimalism

Always remove unnecessary information from the test case. It is important to spend some time on this, if not you are going to waste a lot of people's time. For example, remove unnecessary columns from tables; remove unneeded fields from SQL*Loader control files; remove superfluous functions and code from programs, etc.

Required information

Ensure you include the right information to make your test case reproducible and repeatable. Some suggestions:

SQL and PL/SQL issues or errors

Include the CREATE table statement, provide sample data (INSERT statements) as well as the expected result. Also include the SQL code that's not working and the error you are getting. For PL/SQL errors, provide the code with the error's line number and the method that was used to call it.

Take care that the settings are different for each one, especially with dates (see notes below).

SQL performance problems

Include the SQL statement and an explain plan; provide a list of the table's indexes and useful statistics (number of rows in the table, number of distinct values in the columns...); specify if statistics are gathered and up to date.

Please read this forum post.

SQL*Loader problems

Provide a control file with only the fields required to demonstrate the problem. Also include sample data within the control file (at the end after a BEGINDATA statement).

Program errors

Post the code required to reproduce the error (trim it down, see Minimalism above). Include the error code and line number where the problem occured.

Product version

Always post the product version with 4 decimals like 11.2.0.4.

Notes

About dates

Here's an example of the problems you can encounter with dates. The following works for some people:

SQL> create table t (dt date);

Table created.

SQL> insert into t values ('21-JAN-2021');

1 row created.

But not for others:

SQL> insert into t values ('21-JAN-2021');
insert into t values ('21-JAN-2021')
                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Always use the TO_DATE function and specify a format:

SQL> insert into t values (to_date('21-JAN-2021','DD-MON-YYYY'));

1 row created.

But this is not sufficient as not all people speak the same language:

SQL> insert into t values (to_date('21-JAN-2021','DD-MON-YYYY'));
insert into t values (to_date('21-JAN-2021','DD-MON-YYYY'))
                              *
ERROR at line 1:
ORA-01843: not a valid month

Then you have to either specify your language or use only numeric values:

SQL> insert into t values (to_date('21-JAN-2021','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'));

1 row created.

SQL> insert into t values (to_date('21-01-2021','DD-MM-YYYY'));

1 row created.

You can also use the standard DATE literal which is independent of the national settings:

SQL> insert into t values (date '2021-01-21');

1 row created.

External links

• Test Case on Wikipedia