EnterpriseDB: Getting Data In Oracle Tips by Burleson Consulting

A database application really needs to do two things: get data in and get data out.  Without those two features, there is not much point in having a database.

There are two methods for getting data into the application.  All interfacing is done via stored procedure.  In the first instance, the stored procedure takes parameters and uses those parameters to insert or update the underlying application tables.  The majority of input in TimeTraker is via that type of interface.

A fairly simple example of the pure stored procedure interface, adding a project is based solely on parameters:

PROCEDURE create_tasks(
     p_task_name IN tt_tasks.task_name%TYPE,
     p_task_desc IN tt_tasks.task_desc%TYPE,
     p_active_flag IN tt_tasks.active_flag%TYPE DEFAULT 'Y' )
  AS
  BEGIN
    INSERT INTO tt_tasks (
       task_id, task_name, task_desc,
       active_flag)
    VALUES ( tt_general_sequence.nextval, p_task_name,
             p_task_desc, p_active_flag);
  END;

The second interface is done via XML document.  With the prevalence of XML in the business world today, many applications are programmed to output XML.  XML in one format can easily be converted to alternate formats via XSLT.  That makes XML an ideal format.

The XML interface in TimeTraker is a very simple model of one record per document.  An example doc:

<?xml version="1.0"?>
        <interface>
            <who>Lewis Cunningham</who>
            <what>Task1</what>
            <when>01-JAN-2007</when>
            <hours>8</hours>
        </interface>

The XML document accepts four tags wrapped in the top <interface> tag. The four tags provide who the record is for, what task the hours apply to, when the hours were worked and how many hours were worked.

The procedure that processes the data is just as simple as the XML document.  It loops through each document in the table, extracts the values and applies them.

  PROCEDURE process_xml_data
  AS
  BEGIN
    FOR ci IN (
      SELECT xml_data, interface_id
        FROM tt_xml_interface )
    LOOP   
      add_new_item_update(
        ci.xml_data.Extract('/interface/what/text()').getStringVal(),
        ci.xml_data.Extract('/interface/who/text()').getStringVal(),
        ci.xml_data.Extract('/interface/hours/text()').getStringVal(),
        ci.xml_data.Extract('/interface/when/text()').getStringVal(),
        'Y');

        DELETE FROM tt_xml_interface
          WHERE interface_id = ci.interface_id;

    END LOOP;   

  END;

Because EnterpriseDB does not support the XMLType data type, I will have to take a few extra steps when migrating this code.  For a hint as to how I will migrate it, take a look at chapter 8.

Reporting

Reporting is a critical aspect of any database application.  To keep this application reasonably simple, I have only created five reports.  Those reports are listed in Table 9.4 in the TT_REPORT_OUTPUT package.

As no GUI is included in TimeTraker (that would be a different book), the reports produce a text file rather than screen output.  All of the reports produce both a formatted text file and a comma-separated values (CSV) file suitable for importing into a spreadsheet program. 

The reports all follow the same general flow:

* Open the files

* Display the header in the formatted file

* Write a column header in formatted file

* Write a CSV record

* Write a formatted data record

* Loop until complete

* Close the files

The reports themselves are based in selects against views.  Several of the views use advanced Oracle SQL features and will need to be re-written to run against EnterpriseDB.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.