Course : PL/SQL development, advanced
Practical course - 3d
- 21h00 - Ref. DSP
|
Download in PDF format
Share this course by email
 | Using mass processing in PL/SQL programs |
| Using dynamic SQL |
| Optimizing your PL/SQL code |
| Familiarity with the latest language features (versions 12c, 18c, 19c, 23ai) |
Intended audience
Oracle developers.
Prerequisites
Good knowledge of PL/SQL programming, or knowledge equivalent to that acquired in the course [[Developing in PL/SQL" (ref. ORD).
Course schedule
1 Reminders about PL/SQL
- Predefined data types.
- The different types of cursor.
- DML RETURNING clause.
- Parameterized instructions.
- Sub-programs and application packages.
- Stand-alone transactions.
- Exceptions.
Hands-on work
Write PL/SQL scripts for recovery.
2 Advanced SQL techniques
- Identity, virtual and invisible columns.
- Hints to manage duplicates.
- Deferred constraints.
- Types and object tables.
- IOT tables.
- Partitioned tables.
- Temporary tables.
Hands-on work
Handling the techniques presented.
3 Performance and cache management
- Link variables.
- Associative tables.
- Mass treatments with BULK COLLECT.
- Qualified expressions.
- SQL caching.
- NOCOPY compilation hint.
- PL/SQL functions (cache, table, pipelined, polymorphic).
- SQL macros.
Hands-on work
Use mass processing to improve performance. Check the main aspects of PL/SQL function result caching.
4 Packages
- Identify package components and application contexts.
- Use the STANDARD package and package functions.
- Read files with UTL_FILE. Recompile objects with DBMS_UTILITY.
- Encrypt, decrypt and calculate hash values with DBMS_CRYPTO.
- Manage errors with the DBMS_ERRLOG package.
- Trace an application with DBMS_APPLICATION_INFO.
- Other packages: dbms_profiler, dbms_pipe, dbms_alert, and dbms_rls.
- Compare two implementations with DBMS_PROFILER (RUNSTATS_PKG). Check subroutine side effects.
Hands-on work
Create a script integrating packages to read a file and handle errors. Display source code via the dictionary. Call PL/SQL tables of records in packages.
5 Dynamic SQL
- SQL code execution flow.
- Introduction to dynamic SQL code.
- The EXECUTE IMMEDIATE instruction.
- Declare cursor variables. Dynamic cursors (REF CURSOR).
- DBMS_SQL package.
- Cursor conversions.
Hands-on work
Execute a PL/SQL block dynamically.
6 Procedures, stored functions, triggers
- Modular design. Create, call and delete stored procedures.
- Implement procedure parameters.
- Create, call and delete stored functions. Restrictions on function calls.
- DML triggers (FOR EACH ROW and statement).
- RAISE_APPLICATION_ERROR procedure.
- INSTEAD OF triggers.
- Combined triggers (COMPOUND).
Hands-on work
View procedure information using dictionary views. Create LMD and INSTEAD OF triggers. Implement a combined trigger to resolve the mutation table error.
Customer reviews
4,6 / 5
Customer reviews are based on end-of-course evaluations. The score is calculated from all evaluations within the past year. Only reviews with a textual comment are displayed.
LYSIANE H.
10/09/25
4 / 5
very good :)
ANAIS C.
10/09/25
5 / 5
The training was very interesting and the trainer was very attentive to our needs and questions.
PARTICIPANTS
Oracle developers.
PREREQUISITES
Good knowledge of PL/SQL programming, or knowledge equivalent to that acquired in the course [[Developing in PL/SQL" (ref. ORD).
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
Oracle developers.
PREREQUISITES
Good knowledge of PL/SQL programming, or knowledge equivalent to that acquired in the course [[Developing in PL/SQL" (ref. ORD).
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TEACHING AIDS AND TECHNICAL RESOURCES
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
Dates and locations
Select your location or opt for the remote class then choose your date.
Remote class
Dernières places
Date garantie en présentiel ou à distance
Session garantie