Demo Oracle Application

In today's blog we will create an application based on Oracle Education's training database, the Scott/Tiger schema.

Download Apps Demo
Download Demo App

The "Read Me" guides you through installing the demo application in Oracle Database Express.

Read Me Read Me    

The demo application contains the following:

  • A single schema containing Sales Order and Employee data
  • Additional tables for CSV import, error logging, and defining holiday dates
  • Installation scripts to create the schema, load test data, and compile the PL/SQL code
  • Source Code Templates, PL/SQL packages, SQL reports
  • Demo applications to import and export data via CSV files
  • Functions that do useful things with dates, strings, and numbers

Documentation included:

  • A guide to creating an application development environment
  • Coding Standards, including object naming, and PL/SQL programming tips
  • Database Normalization
  • Specification templates
  • Functional and Technical specs, plus a user guide for the data import app

The application programs have been created using the PL/SQL language. The program modules, functions and procedures, are stored in Packages, which reside in the database.

Public procedures and functions are declared in the Package Specifications, and can be called by database users to perform useful tasks.

Package Name Description
EXPORT Export data to CSV files
IMPORT Import data from CSV files with validation and error handling. Includes Order import and a simple demo
ORDERRP Rules package for Order related functions, for example currentprice returns the price that is currently in effect for a product. This saves repeating code and reduces maintenance effort.
PLSQL_CONSTANTS Define all your non-table related data types and constants here, rather than hard-coding in your programs.
UTIL_ADMIN Admin functions such as error logging.
UTIL_DATE Date manipulation functions. Date of Easter and related holidays. Is today a working day? Last working day of the month.
UTIL_FILE File handling functions, such as load data from a CSV file into a staging table, rename files etc.
UTIL_NUMERIC Number manipulation functions. Base conversion. Factorial. Sort. Convert integer to an alphabetic code.
UTIL_STRING String handling functions. Extract fields from a delimited string (used by the CSV import). Sort strings. Convert escaped characters (e.g. \n to New Line ASCII char 10)

In the next blog post, we will have a look at Normalization, and transforming a sales order spreadsheet into database tables for our demo application.

Home Next Blog