Oracle PL/SQL Developer's Workbook

This workbook is a companion to Steven Feuerstein's bestselling books, Oracle PL/SQL Programming
(considered the bible for PL/SQL developers), Oracle Built-in Packages, and Advanced Oracle PL/SQL:
Programming with Packages. The workbook provides a carefully constructed set of exercises that test PL/SQL
developers' language skills and help them become better programmers. Exercises are provided at three levels:
beginner, intermediate, and advanced; in this way, the book is suitable for complete beginners, yet continues to
be useful as readers gain mastery of the PL/SQL language.

The workbook exercises cover all of the major features of PL/SQL, including:

• Variables and program data
  
• Conditional and sequential control and looping
  
• Database interaction and cursors
  
• Exception handling
  
• PL/SQL records and tables
  
• Functions, procedures, packages, and modular programming
  
• Object features (e.g., object types, nested tables and VARRAYs, object views, and external procedures)
  
• Debugging, tuning, and tracing features

Exercises also cover the new PL/SQL features available in Oracle8i, the "Internet database," (e.g., autonomous
transactions, invoker's rights, bulk binds, and native dynamic SQL), as well as the use of Java and the Web via
PL/SQL.

In addition to the exercises, the workbook provides a primer to the PL/SQL language and a collection of
Feuerstein's best practices for PL/SQL programming.

Table of contents

Preface

I. Problems

1. Declaring Variables and Naming Elements

2. Loops

3. Conditional and Sequential Logic

4. Exception Handling

5. Records

6. Index-by Tables

7. Nested Tables

8. Variable Arrays

9. Object Technology

10. Cursors

11. DML and Transaction Management

12. Cursor Variables

13. Native Dynamic SQL

14. Procedures, Functions, and Blocks

15. Packages

16. Triggers

17. Calling Functions in SQL

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

26. Using Java with PL/SQL

27. External Procedures

28. PL/SQL Web Development

29. Tuning PL/SQL

30. PL/SQL for DBAs

II. Solutions

1. Declaring Variables and Naming Elements

2. Loops

3. Conditional and Sequential Logic

4. Exception Handling

5. Records

6. Index-by Tables

7. Nested Tables

8. Variable Arrays

9. Object Technology

10. Cursors

11. DML and Transaction Management

12. Cursor Variables

13. Native Dynamic SQL

14. Procedures, Functions, and Blocks

15. Packages

16. Triggers

17. Calling Functions in SQL

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

26. Using Java with PL/SQL

27. External Procedures

28. PL/SQL Web Development

29. Tuning PL/SQL

30. PL/SQL for DBAs