MacLochlainns Weblog

I remember when an internal transfer took me away from Oracle 7 and landed me back with DB2. I was indifferent because of the pay increase until I started working in DB2. Then, I kept saying what I couldn’t do with DB2 but could do with Oracle. After all, the Oracle database is the gem in the relational and object relational world (from my perspective).

SQL*Plus is one of the principal if not key reasons why the Oracle database is so functional. PL/SQL is the other. Together, they make an awesome one-two punch. The reason is sometimes less visible to those new or just banging around in Oracle technology. It’s the architecture that feeds every SQL statement through a session (SQL*Plus environment for interactive use), whether interactively or through a submission from a shell script, ODBC, JDBC, or OCI call.

Having been through DBA training for so many releases, as well as development training, I always thought that the drawings from the DBA classes should produce one for developers. Here’s my view of the Oracle Engine from a developer’s perspective.

The drawing is borrowed from the Oracle Database 11g PL/SQL Programming book, dutifully acknowledged (or my acquisition editor would let me hear about it).

Inputs:
Are interactive work at the SQL*Plus prompt or calls from external programming languages. They enter the SQL*Plus environment interactively, where SQL*Plus commands are processed, bind variables are instantiated in the user session, and calls to SQL or PL/SQL engines are spawned. Non-interactive calls work through connections and user sessions.

Outputs:
Are interactive or called sets of values returned by SQL statements or PL/SQL program units. In the case of PL/SQL these are function returns, like expressions.

SQL Engine:
It is where SQL statements are parsed, fetched, and executed against the database. Results or exceptions are returned to the SQL engine, which may then be returned to the user session or PL/SQL engine (for those implicit or explicit cursors).

PL/SQL Engine:
It is where you can write procedure programs to interact with the database, or PL/SQL wrappers to libraries (external procedures written in other programming languages) or internal Java libraries. PL/SQL also lets you manage collections, bulk operations; LOB operations, cursor sets, and anything else that Oracle chooses to implement and extend the features of the database. PL/SQL can call SQL and SQL can call PL/SQL, which is a recursive feature lauded for PHP (not Paul’s Home Page). PL/SQL returns results to a buffer, which you must also open for receipt in the SQL*Plus environment (that’s SET SERVEROUTPUT ON). New developers sometimes call DBMS_OUTPUT.ENABLE thinking that’s all they have to do, then they run there programs. Sometimes they run them a couple times until they realize they need to enable SERVEROUTPUT in the SQL*Plus session. They get a surprise when the output from each prior run is returned. You should always call DBMS_OUTPUT.DISABLE prior to calling DBMS_OUTPUT.ENABLE because it clears the PL/SQL side of the buffer.

External Programs:
These programs (like Pro*C, Java, et cetera) call back into the database to perform SQL or call other PL/SQL program units. There inputs and outputs are more or less managed by the components called.

External Procedures:
These procedures are specialized programs that are registered as libraries in the Oracle database. They must exist in directories authorized in the Oracle database listener.