When I first started writing about the Oracle PL/SQL language back in 1994, there were precious few resources on the language besides Oracle’s documentation. Six years later, the situation has changed radically. Not only does O’Reilly & Associates offer four full-length texts and two quick references on PL/SQL, but other publishers have also focused their attention on this important database programming language for Oracle developers.
Given this state of affairs, you might then reasonably ask: what need does the Oracle PL/SQL Developer’s Workbook fill that thousands of other pages of writings on PL/SQL do not manage to meet?
One concern I have about texts on computer software is that reading a book is inherently a passive activity. You read, and you attempt to absorb information from another, more experienced (you hope) developer. Now, it is true that the act of engaging with the material is not entirely passive. Yet it is also true that until you actually try to write some code or solve some problem with the language, you will not really have tested or integrated your newfound knowledge.
The object of this workbook is to provide you with an active learning experience with PL/SQL. While you could simply read the workbook as you might read my first text, Oracle PL/SQL Programming, the book you are holding has been designed to engage you actively, to provoke a response, to get you solving problems immediately with the techniques at hand.
It is my hope that through your use of this workbook you will deepen your understanding of, and facility with, the PL/SQL language. Consequently, you will move more rapidly along the learning curve and join the ever-growing ranks of PL/SQL experts.
The Oracle PL/SQL Developer’s Workbook is divided into two parts: problems and solutions. I separated the two so that you could concentrate on the problem and come up with your own solution, instead of allowing your eyes to wander over the page and—gosh!—coming across my solution. This design should also make the book more useful in a classroom environment.
Within each part of the book, chapters are grouped into categories as follows:
Chapters Chapter 1 through Chapter 5 contain exercises testing your mastery of language fundamentals:
1. Declaring Variables and Naming Elements
2. Loops
3. Conditional and Sequential Control
4. Exception Handling
5. Records
Chapters Chapter 6 through Chapter 9 contain exercises in the area of data structures:
6. Index-by Tables
7. Nested Tables
8. Variable Arrays
9. Object Technology
Chapters Chapter 10 through Chapter 13 cover database interaction:
10. Cursors
11. DML and Transaction Management
12. Cursor Variables
13. Native Dynamic SQL
Chapters Chapter 14 through Chapter 17 test your expertise in program construction:
14. Procedures, Functions, and Blocks
15. Packages
16. Triggers
17. Calling Functions in SQL
Chapters Chapter 18 through Chapter 25 focus on built-in functionality:
18. Character Functions
19. Date Functions
20. Conversion, Numeric, and Miscellaneous Functions
21. DBMS_SQL Built-in Package
22. DBMS_PIPE Built-in Package
23. DBMS_OUTPUT Built-in Package
24. UTL_FILE Built-in Package
25. DBMS_ JOB Built-in Package
Chapters Chapter 26 through Chapter 30 include the following miscellaneous topics:
26. Using Java with PL/SQL
27. External Programs
28. PL/SQL Web Development
29. Tuning PL/SQL
30. PL/SQL for DBAs
Within each chapter, exercises are organized by level of expertise as follows:
- Beginner
Problems in this section assume very little prior experience with the PL/SQL language and can be used to increase your familiarity with the basic functionality of the language.
- Intermediate
You have been working with PL/SQL for a while and feel comfortable with finding your way around the built-in functions, cursor FOR loops, etc. The problems in this category will stretch your comfort level, confirm your knowledge, and take you in new directions.
- Expert
Do you have a few minutes to spare to meet a challenge? The problems in this category often require that you apply your knowledge in new and creative ways. Some will take a fair amount of effort, but I hope you will then be able to apply the resulting code to your development environment.
In all cases, I have made an effort to design problems that engage and entertain you. I have always felt that programming can be (almost obsessively) fun to do and extremely satisfying.
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
