CODE: Programming project required

From: James Rogers (
Date: Mon Jan 22 2001 - 13:17:00 MST

At 11:39 PM 1/22/2001 +0930, Emlyn wrote:
> > Errrr....I take it that you don't really know how to use SQL*Plus? Things
> > like conditionals, loops, etc are all supported natively inside SQL*Plus
> > without doing any of the stuff you are talking about.
>Damn, caught out; I really don't know it well at all, apparently. Do you
>have some good links to resources on this? The ones I found were less than
>good. Loops? Conditionals? Do I have to beg?

Heh. Seriously, people who have been using Oracle for years usually don't
realize just how much they don't know. Most people consider me to be an
expert on Oracle development and I *still* regularly learn new and useful
things about the product. I don't know of any links to resources, but some
of the third party books like O'Reilly's are good (avoid Oracle's books, as
they are basically tree dumps of the online docs).

To get the most out of the Oracle environment you need to know a few
things. Most importantly, you need to learn PL/SQL and learn it
well. PL/SQL is an Ada-like language (should be trivial for you, since you
know Delphi), is very easy to learn for an experienced programmer, and
gives you all the procedural constructs you need. However, it has some
quirks and oddities that nothing but experience will teach. The O'Reilly
book (PL/SQL Programming) is the best reference on the language that I know
of. The beauty of PL/SQL isn't that it is a wonderful language (it isn't
-- much too verbose), but that you can embed PL/SQL procedural blocks into
just about everything in Oracle. You can mingle PL/SQL code with your
SQL/commands/environment/etc in SQL*Plus (what you are looking for). You
can use PL/SQL to extend the SQL syntax (e.g. you can use custom procedural
logic in your WHERE clauses). There are also a bunch of standard libraries
in PL/SQL that allow you to interact with various parts of the system
(scheduling jobs, accessing files, etc). Additionally, you can link shared
libraries into Oracle and map the call interfaces to PL/SQL to extend the
functionality and effectively give an unlimited number of
capabilities. And generally speaking, nothing runs faster for doing
procedural database logic, though it is dog slow for many other things
(like arithmetic). The details of how to do all these things is in the

The other thing you need to learn is SQL*Plus. This is a pain, but
necessary. PL/SQL lets you do what you want, but SQL*Plus sets up the
environment and controls the scripts. But it isn't as important to learn
SQL*Plus very well as it is to learn PL/SQL very well; if you don't know
PL/SQL, you are missing out on a significant portion of the functionality
in Oracle. When everything is set up right and you know what you are
doing, you end up with a relatively rich scripting environment that allows
you to build your own components and tools in a fashion similar to Unix

>"DBAs exist to keep developers from getting bored." - can I have this as a


>Do I remember that you do really scary code generation stuff? The memory is

Yes, but my bread-n-butter is system and application architecture on big
Oracle systems.


-James Rogers

This archive was generated by hypermail 2b30 : Mon May 28 2001 - 09:56:22 MDT