Introduction and refresher
- Transactional processing in Oracle.
- Objects and dictionary of an Oracle database.
- SQL and PL/SQL. Overview of PL/SQL.
- Tools used. Oracle documentation.
Language elements
- Host environments. Introduction to syntax Declaring PL/SQL variables and constants.
- Types of data and conversion. Predefined functions.
- Control instructions: IF- ELSE, WHILE loops, FOR loops.
- Composite types: records, indexed tables.
- Object types. Defining subprograms.
- Configuring subprograms.
Exercise
Declaring variables. Changing the date. Typing attributes Using collections and their methods.
Access to the database
- The database and the notion of a cursor.
- Access via implicit cursor: update, delete, insert, select. Using RETURNING and BULK COLLECT clauses.
- Collections and FORALL.
- Cursor FOR loops. Update cursors.
- Explicit cursors. Cursor variables.
Exercise
Using explicit cursors and BULK COLLECT.
Stored procedures
- Managing stored subprograms, stored libraries and packages
- Managing stored procedures: Access rights, dictionary views, and dependency.
- Using packages.
Exercise
Creating procedures, stored functions, and packages.
Error management
- Handling an exception.
- Using RAISE_APPLICATION_ERROR.
- Triggering an exception via RAISE.
- Propagation and transaction management.
Exercise
Using Raise and RAISE_APPLICATION_ERROR.
Triggers
- Definition and types of Event/Action triggers. Order triggers.
- Lines (using: NEW and: OLD), E/C/A (Event/Condition/Action).
- “INSTEAD OF“ on views, on system events.
- Using autonomous transactions.
Exercise
Creating order triggers and line triggers.
Complements
- Dynamic SQL. Uusing dbms_application_info and WRAP to hide the source code.
3d
- 21h00 
