BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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' )
    INSERT INTO tt_tasks (
       task_id, task_name, task_desc,
    VALUES ( tt_general_sequence.nextval, p_task_name,
             p_task_desc, p_active_flag);

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"?>
            <who>Lewis Cunningham</who>

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
    FOR ci IN (
      SELECT xml_data, interface_id
        FROM tt_xml_interface )




        DELETE FROM tt_xml_interface
          WHERE interface_id = ci.interface_id;

    END LOOP;   


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 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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter