Oracle Programming Concepts

We are all acquainted with SQL as the language understood by relational databases like Oracle. We are all pretty happy that we have such a language at our disposal to manipulate databases. SQL is a powerful, easy-to-use language that uses the same daily-use type of language.

Before the widespread acceptance of SQL, database programming didn't exactly look like the simple English-language style queries and logical operations that we are accustomed yo using. C programmers would write entire database applications with their own C routines, employing their own code to provide structures like tables, views, and indexes, as functionality such as insert and delete. All these features were available in their databases also, but it was all done the hard way, with C datatypes like long and char, and the user was given a front-end for data entry and other types of interaction. He was not able to interact directly with the database engine (which was often a large multi-threaded C-program). Besides providing the basic features, the developers had to write custom code to handle errors and such.

Oracle was the first to formalize the process of implementing a database in the C programming language. They finally came out with a relational database in the 1970s, written entirely in C. Over the years they have added feature after feature to their exceedingly complex database, and nowadays Oracle is implementing most of the fancy stuff in Java. Still, the core of Oracle is build in C, which is why all Oracle installations require a C compiler (if you have installed Oracle on Linux or Unix, then you would recall the requirements of gcc/glibc etc). As it has been written in C, the Oracle code is exceedingly fast. The SQL engine of Oracle is comparable, or faster than, C programs (written using Pro*C) accessing the Oracle database.

One of the key features of Oracle — a crucial decision made way back in the '80s by the designers of the evolving Oracle database — was to implement the new Structured Query Language (SQL) standards. This has become a key feature of the Oracle database. Oracle was one of the first databases to support SQL. However, SQL was found to have some limitations when compared to existing third generation computer languages such as C. The most glaring of its limitations is that it does not allow procedural constructs such as the looping constructs supported by third-generation languages such as C or Fortran. (You might recall that an SQL statement that you use in sqlplus, such as a "select * from emp where ename='SMITH';" is just that - a one-off statement. For retrieving another record, you have to issue another statement. For processing record-wise, this is a pain). This is a great drawback, and, considering that SQL is a fourth generation language, this was a big handicap.

Naturally, Oracle decided to circumvent this deficiency. It wrote a procedural language extension to SQL, which is named, understandably, PL/SQL (Procedural Language Extensions in PL/SQL). Now you had something very close to a real programming language, and it was operating "very close" to the Oracle database itself. (The SQL engine would directly access the Oracle memory structures). Specifically, you could now use procedures and packages in the database. The concept of code reuse and attendant performance optimization was thus introduced to an already respectable database.

With this much of background into the evolution of PL/SQL and why it was developed, we can now proceed to the heart of the matter: using PL/SQL in our day-to-day work.

To interact with the Oracle database (database = memory structures + data files), Oracle provides one standard utility which is available in all platforms. It is the SQL*Plus, invoked from the command line (as oracle user in UNIXes and UNIX-like systems) by typing sqlplus. In the Windows environment too this is available, as sqlplus located in

To have a serious interaction with the Oracle database from your client PC, we recommend using command-line sqlplus. It is perhaps the best command-line program available on Windows.

C:\Documents and Settings\Owner>sqlplus

The command line is the only legitimate way to enter the Oracle database and be serious about the business. The Windows command line is probably the best way to use sqlplus, with its command history, flexible cut and paste options, etc. We hope that this point will be taken seriously, as the "GUI" version (sqlplusw) is a lame wrapper for the sqlplus executable. Furthermore, it has been desupported since September 2005.

The basic PL/SQL unit of execution, one that you can use straight away from the sqlplus prompt, is the PL/SQL block. It has the following structure.

declare
-- you declare cursors and program variables here
begin
-- statements go here
exception
-- exception handling statements go here
end;

Of these four key statements, begin and end; are mandatory, while declare and exception are optional. To execute a PL/SQL block, you must terminate it with a forward slash (/). SQL*Plus then sends the block to the database for execution.

Now we shall look at a sample PL/SQL code block. This is the code that you type into the sqlplus prompt.

declare
phno number(7);
begin
phno := 2454325;

insert into address_book (name, phone_no, location)
values ('HARI KAIMAL', phno, 'IT CELL ANNEXE');

commit;
end;

The actual sqlplus screendump is given below.

SQL> declare
2 phno number(7);
3 begin
4 phno := 2454325;
5
6 insert into address_book (name, phone_no, location)
7 values ('HARI KAIMAL', phno, 'IT CELL ANNEXE');
8
9 commit;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> select count(*) from address_book;

COUNT(*)
----------
1

Okay. Now we have produced our first block of PL/SQL on the sqlplus prompt. The semicolon after the end signals the sqlplus engine to execute the code, and we have our dml statement insert the data into the address_book table. This might seem like a lot of overhead, especially when you can simply write:

SQL> insert into address_book values ('HARI KAIMAL', 2464724, 'BSNL STR QTRS');

1 row created.

Still why do we go to all the lengths, declaring the variable, formally beginning the transaction, assigning a value to our variable(s), and then issuing the insert statement?

It is obvious that there must be some advantages to this procedural approach. If simple insert, update and delete were all the things that were possible with Oracle, then it would have been OK... but for that we don't need Oracle, or for that matter, any expensive database! A few thousand lines of C code would be enough to implement such functionality, and flat files would serve as the database tables.

The first thing we realise is that database programming is not about issuing random queries. In a well-implemented database application, such occasions to issue one-off queries should be rare. It reveals some nefarious activity - namely, the back-end laundering of form data. Needless to say, it is equivalent to bypassing the safety checks and whatever logic you have built up in your database application. It should be avoided at all costs. (It is habitual for us at the IT Cell to consider all our applications as "web applications" whereas they are all, in the real sense, database applications. Considering them as web applications - considering that 99% of all network applications in the world are web-applications - insulates from a grave sin: the fact that we are not using the database to do things which it is best equipped to do. Instead we, devise our own ways of manipulating SQL before sending it to the database. And still pay $40,000 per processor for the Oracle licence).

If we want record-level data processing, for example, we can't do it with a one-off query (unless it is a straight update - in which case also a PL/SQL procedure is more desirable). An evident advantage is that we are executing the insert as a procedure, and not as a straight transaction. Such execution gives us a lot of latitude in what we do with the DML. To use a simple example, if we issue a bad DML in a one-off insert, such as

SQL> insert into address_book values (
2       'HARI KAIMAL', 2464724, 'BSNL STR QRTRS', 'TVM');
insert into address_book values (
           *
ERROR at line 1:
ORA-00913: too many values