Oracle Database 12c is more than just a database management system - it’s also an engine for many programming languages. Not only does it serve as a Java engine with the built-in Java Virtual Machine (JVM), it’s a PL/SQL engine as well. This means that the code you write can be stored in a database and then run as required when it is needed.
The PL/SQL engine is bundled together with the database and is an integral part of the Oracle server, providing you with a powerful language to empower your logic and data. Let’s look at how PL/SQL fits into the Oracle server. Figure 1 shows you how PL/SQL works from both within and outside the Oracle database.
FIGURE 1. PL/SQL architecture
At the center of the Oracle Database 12c server in Figure 1 is the primary engine for the database, which serves as the coordinator for all calls to and from the database. This means that when a call is made from a program to the server to run a PL/SQL program, the Oracle server loads the compiled program into memory and then the PL/SQL engine and SQL engine execute the program. The PL/SQL engine will handle the program’s memory structures and logical program flow and then the SQL engine issues data requests to the database. It is a closed system and one that allows for very efficient programming.
PL/SQL is used in numerous Oracle products, including the following:
- Oracle Database Server
- Application Express
- Oracle Data Miner
- Oracle Warehouse Builder
- Oracle Data Integrator
- Oracle eBusiness Suite
- Oracle Portal
All of these programs use PL/SQL to some extent. If you look at the internals of Oracle applications, you’ll see that there can be as many as five million lines of PL/SQL code contained within it. PL/SQL interfaces can be developed and utilized from these Oracle development environments:
- SQL*Plus
- SQL*Developer
- Oracle Grid Control/Oracle Enterprise Manager
- Oracle Pre-compilers (such as Pro*C, Pro*COBOL, and so on)
- Oracle Call Interface (OCI)
- Server Manager
- Oracle Application Server 12c
- jDeveloper
As you can see, PL/SQL is well established within Oracle’s line of products. The reasons for using PL/SQL are primarily its tight integration with the database server and its ease of use. You will find that there are few tasks that PL/SQL cannot handle.
TIP
Use PL/SQL to program complex tasks or for program elements that may be used over and over again.
