1.2.1. The Early Years of PL/SQL
In Oracle’s early years, the declarative approach of SQL, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations rose, and requirements became more stringent. Developers needed to get “under the skin” of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database scripts.
In 1988, Oracle Corporation released Oracle Version 6, a major advance in its relational database technology. A key component of that version was the so-called “procedural option” or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.० incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural,straightforward manner.
This first release of PL/SQL was very limited in its capabilities। On the server side, you could use PL/SQL only to build”batch-processing” scripts of procedural and SQL statements. You could not construct a modular application or store business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output).
1.2.2. Improved Application Portability
The concern about portability might seem odd to those of us familiar with Oracle Corporation’s marketing and technical strategies। One of the hallmarks of the Oracle solution from the early 1980s was its portability. At the time that PL/SQL came along, the C-based RDBMS ran on many different operating systems and hardware platforms. SQL*Plus and SQL*Forms adapted easily to a variety of terminal configurations. Yet for all that coverage, there were still many applications that needed the more sophisticated and granular control offered by such host languages as COBOL, C, and FORTRAN,As soon as a developer stepped outside the port-neutral Oracle tools,the resulting application would no longer be portable.The PL/SQL language was (and is) intended to widen the range of application requirements that can be handled entirely in oepareting system-independent programming tools. Today, Java and other programming languages offer similar portability. Yet PL/SQL stands out as an early pioneer in this field and, of course, it continues to allow developers to write highly portable application code।
1.2.3. Improved Execution Authority and Transaction Integrity
An even more fundamental issue than portability was execution authority. The RDBMS and the SQL language let you tightly control access to, and changes in, any particular database table. For example, with the GRANT command, you can make sure that only certain roles and users can perform an UPDATE on a given table. On the other hand, this GRANT statement can’t ensure that the full set of UPDATEs performed by a user or application is done correctly. In other words, the database can’t guarantee the integrity of a transaction that spans more than one table, as is common with most business transactions.The PL/SQL language provided tight control and management over logical transactions. One way PL/SQL does this is with The implementation of execution authority. Instead of granting to a role or user the authority to update a table, you grant privleges only to execute a procedure, which controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account (the “definer” of the program), which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the “gatekeeper” for the transaction. The only way that a program (whether it’s an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. In this way, the overall application transaction integrity is guaranteed.
1.2.4. Humble Beginnings, Steady Improvement
As powerful as SQL is, it simply does not offer the flexibility and power developers need to create full-blown applications. Oracle’s PL/SQL language ensures that we can stay entirely within the operating system-independent Oracle environment and still write highly efficient applications that meet our users’ requirements.
PL/SQL has come a long way from its humble beginnings. With PL/SQL 1.0, it was not uncommon for a developer to have to tell his or her manager, “You can’t do that with PL/SQL.” Today, that statement has moved from fact to excuse. If you are ever confronted with arequirement and find yourself saying, “There’s no way to do that,” please don’t repeat it to your manager. Instead, dig deeper into The language, or explore the range of PL/SQL packages offered by Oracle. It is extremely likely that PL/SQL today will, in fact, allow you to do pretty much whatever you need to do.
1.2.5. The Significance of Oracle Database 10g PL/SQL
Oracle Database 10g PL/SQL is impressive, but not particularly because it offers many new language features and extended capabilities. Instead, it demonstrates Oracle’s continuing strong commitment to supporting this language and the millions of PL/SQL।The PL/SQL development team spent over five years redesigning the compiler to support three critical capabilities:
Automatic, transparent optimization of code
Starting with Oracle Database 10g Release 1, Oracle automatically restructures our code to improve its performance. After extensive testing and benchmarking, Oracle estimates that the non-SQL portions of your PL/SQL program can run twice As fast as they did in Oracle9i Database.
Starting with Oracle Database 10g Release 1, the compiler can detect potential runtime problems with your code, such As identifying lines of code that will never be run. This process, also known as lint checking, has long been desired by PL/SQL developers.
Starting with Oracle Database 10g Release 2, within your PL/SQL programs you can now specify conditional logic that is processed as your code is compiled. This “ifdef” feature allows you to, among other things, specify different code to be compiled for different versions of Oracle (which will come in handy primarily in future releases of Oracle).