Oracle Best Practices Part 3

In this series during Part 1 I mentioned below best practices;
1- Put your Database into ARCHIVELOG Mode
2- Comment on Tables and Columns similar to your PL/SQL or SQL Source Codes
3- Use DBMS_STATS for statistic collection
4- Using Recovery Manager(RMAN) for Backup and Recovery needs

And following Part 2 I mentioned below best practices;
5- Resumable Statements and Space Allocation
6- Maintenance of Global Partitioned Indexes
7- Default Tablespaces
8- PL/SQL Bulk Operations
9- Locking Issues
10- Oracle System Event Triggers
11- Autonomous Transactions

12- Learn what is already provided with PL/SQL language

Each database has its own procedural language and PL/SQL(Procedural Language/Structured Query Language) is Oracle’s server-based procedural extension to the SQL database language, it is Oracle’s native programming language and its syntax strongly resembles that of Ada.

PL/SQL supports variables, conditions, arrays and exceptions etc. also implementations from version 8 onwards have included features associated with object-orientation. PL/SQL stored procedures(functions, procedures, packages, and triggers) which perform data manipulation get compiled into an Oracle database, to this extent their SQL code can undergo syntax-checking and can be shared which is critical for scalability needs. PL/SQL offers several pre-defined packages for specific purposes, which enables developers to answer the time to market needs, no need to reinvent a dumper wheel :) Even some complex administration tasks like scheduling, data load/unload, change data capture configuration, backup metadata or transport tablespaces between Oracle databases are easily done and may be scripted by these supplied packages. Also you can easily develop some web based applications over these packages on apex or .net in minutes :)

PL/SQL is powerful, Oracle’s advanced replication, applications, workflow solutions and lately web based rapid application development platform Application Express were build by PL/SQL. PL/SQL data types are SQL data types, so no conversions needed and you have tight coupling between the language and the database(cursor for loops for example). You are protected from changes in the database, PL/SQL can survive database schema changes such as a varchar2(80) -> varchar2(255) easily by %TYPE configuration, sub-optimizations like cursor caching done for you and in PL/SQL it is very hard to not use bind variables correctly and don’t forget the dependency mechanism you get.

PL/SQL provides full portability, you can start coding at weekend on your windows express edition laptop and port it to your solaris enterprise edition development database on monday, so Oracle easily becomes your virtual machine here :)

After this introduction I want to share my “database independence” point of view. Lets say your customer needs an application which will create, update and query their customers’ information. So some of your functions will be fnc_get_customer_name_by_id, fnc_set_customer_name_by_id etc. Here for the independence need these functions may be implemented at such a layer that is designed for any kind of database, like Java Database Connectivity(JDBC) for instance. But we already know Oracle is not like others, so the question is how to use efficiently and utilize the money purchased for the database software and still be ready to run for other database vendors?

At this point lets assume Oracle database itself as an application designed for our needs, have you ever needed to create, update or query directly the dictionary ever after? Here lies a best practice to investigate, we use supplied packages and DDL scripts to manipulate the dictionary and v$, all% views to query information. So I will advice the “database independence” solution this way, if you want to sell customer friendly products you have to utilize the database application they have invested, use its all available features for performance and security. So start creating procedural APIs, with PL/SQL of course for Oracle, package body’s may be wraped like Oracle does for its packages if you need to close the sources, and call these interfaces from whatever technology you want to use. These may be web services, .net, php, apex, java etc. in time, now you are also technology independent :)

Lets give an example here, with the support of DBMS_SQLTUNE package you may use SQL*Plus, Oracle Enterprise Manager, Toad like IDE for Automatic Sql Tuning need, or even after you can easily prepare an IDE with ApEx or a plugin for Eclipse.

In order to move on to another database vendor you create these get and set database interfaces with their procedural language, take the benefits of the other database language provides. This way of database application development methodology in short run seems to be costly since who as the qualified human resource to write all these database APIs for each vendor, but remember each database is different and if your customer purchased Oracle you have to think about when you are marketing your application as “..this software is so beautifully designed that without any change it can also run on MySQL..”

Before closing I want to mention the power of PL/SQL packages for encapsulation and reusability needs. Before starting to any database application first you need to design your kernel packages like error&exception management, transaction management, log management etc. These might be completely parametric since depending on the needs they change frequently so they need to be easily configurable. Also with packages you have your libraries, so nothing is excepted to be repeating and this favors reusability, code sharing at runtime and scalability. Check PLVision for an example implementation.

As a conclusion I may advice you that choosing the data access layer for your database application projects is very important, PL/SQL for Oracle is inevitable since;
– this is the best language your software will talk,
– encapsulation and packaging of your SQL needs into PL/SQL will be beneficial for your security and performance needs, especially for the scalability,
– the most strategic outcome you will be facing in long-run is while trying to be “database independence” you will exactly become some software house dependent, they will charge you as they like and give unbelievable schedules for any kind of small activity critical your business need, this is their dream come true, they will be the master and you will be the puppet! But if you force them to have this data layer you find yourself building up new applications using the supplied data access interface in the future with any kind of technology you prefer independently from these “jackals”.

Be careful, don’t let any software house to use your company’s resources as a laboratory, since their priority will be write once sell the product as many as possible.

For more reading please see Steven Feuerstein‘s resources. He is an expert on the Oracle PL/SQL language, and the author or coauthor of nine books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices;
Oracle PL/SQL Programming
Best Practice PL/SQL

And an example comparison between a java and a pl/sql data access layer;
SDPA getAllSubsbyMsisdn method.pdf

Continue reading with Part 4

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

Refences Used :
Oracle® Database Application Developer’s Guide – Fundamentals 10g Release 2 (10.2)
Oracle® Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2)
Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s