EnterpriseDB: Getting Data In
Oracle Tips by Burleson Consulting
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.
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.
fairly simple example of the pure stored procedure interface, adding
a project is based solely on parameters:
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' )
INSERT INTO tt_tasks (
task_id, task_name, task_desc,
VALUES ( tt_general_sequence.nextval, p_task_name,
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.
XML interface in TimeTraker is a very simple model of one record per
document. An example doc:
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.
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.
FOR ci IN (
SELECT xml_data, interface_id
FROM tt_xml_interface )
DELETE FROM tt_xml_interface
WHERE interface_id = ci.interface_id;
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 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
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
file suitable for importing into a spreadsheet program.
reports all follow the same general flow:
Display the header in the formatted file
Write a column header in formatted file
Write a CSV record
Write a formatted data record
Close the files
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.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.