Test the contents of an operating system file
This tutorial shows how you can easily set up a test to check the contents of a file against another, control file: does it match? Is it different?

Flash tutorial explaining the topic: A brief video that shows you how to use Quest Code Tester to solve the problem posed by this topic.

Step-by-step instructions: Follow these steps to replicate what we show you in the tutorial.

Steps

For this tutorial to work in your environment, you will need to be able to read and write files with UTL_FILE. This means you need execute authority on that package and you need to set up at least one directory for read/write from within UTL_FILE. This can be done through the UTL_FILE_DIR startup parameter or the creation of a schema-level directory object.

For the duration of this tutorial, I will assume you have set up a directory in the database TEMP. On my laptop, it points to c:\temp.

From the Test Dashboard, create a new test definition by clicking on New from the Test Definition Programs right-click menu . Select qctod#file_io from the list and press OK. Now you are inside Test Builder. Use the Program Details toolbox to set the current program to COPY_FILE.

Set up the test cases

Before diving into the details of each of the test cases, use "top down design" to brainstorm the various test cases that you will need. You do this in the Test Case list:

                           

Press the "Add test case" button  to create a new test case and rename the test case for each of the following names:

  • Successful copy of file temp.txt
  • Unsuccessful copy of file temp.txt

Now it is time to provide the details of each test case. For each of the headers below, I assume that you will click on the corresponding test case name, and then shift focus to the Test Case Editor to fill in the blanks.

Successful copy of file temp.txt

  • Move to the Inputs grid. For DIR_IN, enter "TEMP". Note: database directory names must be UPPER CASE or you will get an error.
  • For ORIG_FILE_IN enter "temp.txt"
  • For NEW_FILE_IN, enter "temp2.txt"
  • Move to the Outcomes grid. Create a new outcome with a right click "Add outcome". Choose "File" for the type of data that is changed by the program.
  • Click on […] (on the left hand side) to bring up the Properties window for this file. Enter "TEMP" for the directory and "temp2.txt" for the file name. Press OK.
  • Click on […] (on the right hand side, in Expected Reults) and view the properties window. Enter "TEMP" for the directory and "temp.txt" for the file name. Press OK.

That clearly defines how we want to verify the copy. But there is one outstanding issue: how do I know for sure that there is a file named temp.txt to copy? I shift my attention back up to the test case properties. Under the name, you can see several tabs. These allow me to enter a description, but also chunks of code that will be automatically inserted into various parts of the test package, according to their purpose. So…

  • Click on Subprograms and enter the following code:

PROCEDURE create_file (

   loc_in  IN VARCHAR2,

   file_in IN VARCHAR2 ,

   line_in IN VARCHAR2

   )

IS

   l_fid UTL_FILE.FILE_TYPE;

   l_line VARCHAR2(32767);

BEGIN

Steps

For this tutorial to work in your environment, you will need to be able to read and write files with UTL_FILE. This means you need execute authority on that package and you need to set up at least one directory for read/write from within UTL_FILE. This can be done through the UTL_FILE_DIR startup parameter or the creation of a schema-level directory object.

For the duration of this tutorial, I will assume you have set up a directory in the database TEMP. On my laptop, it points to c:\temp.

From the Test Dashboard, create a new test definition by clicking on New from the Test Definition Programs right-click menu . Select qctod#file_io from the list and press OK. Now you are inside Test Builder. Use the Program Details toolbox to set the current program to COPY_FILE.

Set up the test cases

Before diving into the details of each of the test cases, use "top down design" to brainstorm the various test cases that you will need. You do this in the Test Case list.                         

Press the "Add test case" button  to create a new test case and rename the test case for each of the following names:

  • Successful copy of file temp.txt
  • Unsuccessful copy of file temp.txt

Now it is time to provide the details of each test case. For each of the headers below, I assume that you will click on the corresponding test case name, and then shift focus to the Test Case Editor to fill in the blanks.

Successful copy of file temp.txt

  • Move to the Inputs grid. For DIR_IN, enter "TEMP". Note: database directory names must be UPPER CASE or you will get an error.
  • For ORIG_FILE_IN enter "temp.txt"
  • For NEW_FILE_IN, enter "temp2.txt"
  • Move to the Outcomes grid. Create a new outcome with a right click "Add outcome". Choose "File" for the type of data that is changed by the program.
  • Click on […] (on the left hand side) to bring up the Properties window for this file. Enter "TEMP" for the directory and "temp2.txt" for the file name. Press OK.
  • Click on […] (on the right hand side, in Expected Reults) and view the properties window. Enter "TEMP" for the directory and "temp.txt" for the file name. Press OK.

That clearly defines how we want to verify the copy. But there is one outstanding issue: how do I know for sure that there is a file named temp.txt to copy? I shift my attention back up to the test case properties. Under the name, you can see several tabs. These allow me to enter a description, but also chunks of code that will be automatically inserted into various parts of the test package, according to their purpose. So…

  • Click on Subprograms and enter the following code:

PROCEDURE create_file (

   loc_in  IN VARCHAR2,

   file_in IN VARCHAR2 ,

   line_in IN VARCHAR2

   )

IS

   l_fid UTL_FILE.FILE_TYPE;

   l_line VARCHAR2(32767);

BEGIN

   l_fid := UTL_FILE.FOPEN (loc_in, file_in, 'W');

   utl_file.put_line (l_fid, l_line);

   utl_file.fclose (l_fid);

END create_file;

This is a simple utility that creates a file with a single line of text in it, then closes the file. I will use this program to initialize the temp.txt file, as follows:

  • Click on Initialization and enter this text:

create_file ('TEMP', 'temp.txt', 'This is the contents of temp.txt');

  • Next test case!

Thus, I create another program, a utility, for me to use right inside this test (I could define it outside the package, but then I have to worry about whether that utility will be present "out there").

Then I use the program to initialize the test case. That is, every time this test case is run, the code will be called to create the file, and then the test can proceed.

Unsuccessful copy of file temp.txt

Now let's see how I can test to make sure a file is not there.

  • Move to the Inputs grid. For DIR_IN,, enter "PERM." This is an undefined directory name. UTL_FILE will throw an error when it tries to write to this location. The copy_file program simply ignores the error, but does not perform the copy.
  • For ORIG_FILE_IN, enter "temp.txt"
  • For NEW_FILE_IN, enter "temp2.txt"
  • Move to the Outcomes grid. Create a new outcome with a right click "Add outcome". Choose "File" for the type of data that is changed by the program.
  • Click on […] (on the left hand side) to bring up the Properties window for this file. Enter "PERM" for the directory and "temp2.txt" for the file name. Press OK.
  • Change the operator to "does not exist".  You then do not need to provide any information on the right hand side of the grid.
  • All done!

Time to save/run your test!

You can now save your test definition and run your test with a single press of the Save, Close, Run button  in the Test Cases toolbar:

You will then see the results in the Results Viewer. Close the Results Viewer when finished viewing the test results.

Source code of program tested: Compile this code in your schema so that you can build your test definition for it. 

CREATE OR REPLACE PACKAGE qctod#file_io
IS
   PROCEDURE copy_file (
      dir_in IN VARCHAR2
    , orig_file_in IN VARCHAR2
    , new_file_in IN VARCHAR2
   );

   FUNCTION file_exists (dir_in IN VARCHAR2, file_in IN VARCHAR2)
      RETURN BOOLEAN;
END qctod#file_io;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY qctod#file_io
IS
   TYPE fgetattr_t IS RECORD (
      fexists BOOLEAN
    , file_length PLS_INTEGER
    , block_size PLS_INTEGER
   );

   PROCEDURE copy_file (
      dir_in IN VARCHAR2
    , orig_file_in IN VARCHAR2
    , new_file_in IN VARCHAR2
   )
   IS
   BEGIN
      UTL_FILE.fcopy (dir_in, orig_file_in, dir_in, new_file_in);
   EXCEPTION
      WHEN OTHERS
      THEN
         -- Ignore the problem. Nothing copied....
         NULL;
   END copy_file;

   FUNCTION file_exists (dir_in IN VARCHAR2, file_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      fgetattr_rec fgetattr_t;
   BEGIN
      UTL_FILE.fgetattr (LOCATION         => dir_in
                       , filename         => file_in
                       , fexists          => fgetattr_rec.fexists
                       , file_length      => fgetattr_rec.file_length
                       , block_size       => fgetattr_rec.block_size
                        );

      -- Bug #2240685. fexists is always returned TRUE
       -- But non-existent file has ZERO file_length and block_size
      IF fgetattr_rec.file_length = 0 AND fgetattr_rec.block_size = 0
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   -- When the bug is fixed, comment OUT the above
   -- and uncomment this:
   -- RETURN fgetattr_rec.fexists;
   END file_exists;
END qctod#file_io;
/

SHO ERR