WARNING: This web is not used anymore. Please use PDBService.GeneralAdvices instead!

Checklist for well written Oracle application

Use bind variables

  • Place-holder variables in SQL statements that are substituted with concrete values before statement execution.
  • Coding your queries using bind variables will speed up your application by avoiding the repeated parsing of the similar queries.
  • Bind variables tutorial
  • Using Bind variables with OCI

Use primary keys

  • Column or set of columns that uniquely identifies table rows (ie differs for rows in a table) and can by used to quickly find a row
  • Every table should have a primary key defined
  • Oracle documentation - Choosing primary key

Define foreign keys (and add indices)

  • Column that is referenced from another table of the schema (eg. TableOrder(OrderID)=TableOrderedItems(OrderId) )
  • Define a foreign key constraint to allow the database to insure referencial integrety
  • Add indexes on foreign key columns to speed up access for larger tables
  • Oracle documentation - Mantain data integrity



  • In case the application will use several short request to the database then implement connection pooling so that persistent connections to the database are created.
  • The application should also be able to retry to reconnect to the database in case the session was dropped (due network problem, expired idle time, etc).
  • The application should ensure that there is a single physical connection to a given database. Multiple transactional contexts should be handled using multiple user sessions for the same physical connection.

Bulk operations

Normalized schema

Fully qualified names

  • Use the syntax "schema_name.table_name" in the FROM clause of yours queries. This prevents Oracle errors seen in the past.

Configuration file

  • external and easy to edit configuration file with connection string and usernames

Tutorials and presentations:

Edit | Attach | Watch | Print version | History: r19 < r18 < r17 < r16 < r15 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r19 - 2008-06-17 - MiguelAnjo
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2023 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback