How to Handle Insert Failures in PL/SQL with Custom Exception Management

Описание: Learn how to gracefully handle insert exceptions in PL/SQL by incrementing department IDs and inserting new records with custom error handling.
---
This video is based on the question https://stackoverflow.com/q/63085801/ asked by the user 'MAYANK RAJ' ( https://stackoverflow.com/u/13892995/ ) and on the answer https://stackoverflow.com/a/63086675/ provided by the user 'Roberto Hernandez' ( https://stackoverflow.com/u/13755538/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: plsql for inserting when exception is encountered

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Handling Insert Failures in PL/SQL with Custom Exception Management

In the world of application development, data integrity is paramount. A common challenge developers encounter is handling exceptions that arise when attempting to insert records into a database. This guide will guide you through a practical example of how to manage such exceptions in PL/SQL when inserting a new department record.

Problem Statement

Consider the scenario where you want to insert a new department into a dept table with the values (NULL, 'LOGISTICS', 'SINGAPORE'). However, an attempt to insert a record where the primary key (department number) is NULL will raise an exception. The goal is to handle this exception gracefully, increment the maximum deptno found in the table, and retry the insertion with the new department number.

Solution Overview

To effectively tackle this issue, we can follow these key steps:

Define Custom Exceptions: Handle specific errors using PL/SQL exception handling.

Insert Data with Error Management: Attempt the insertion and catch exceptions as they arise.

Determine the Maximum Department Number: If an error occurs, retrieve the current maximum department number and increment it.

Reattempt the Insert: Use the newly calculated department number to insert the record again.

Step-by-Step Code Explanation

Here's the refined PL/SQL code that implements the solution:

[[See Video to Reveal this Text or Code Snippet]]

Code Breakdown

Exception Handling:

e_insert_excep is defined to handle the NULL primary key exception (error code -1400).

Using pragma exception_init, we link the exception handler to a specific error.

Insertion Logic:

The initial insert statement tries to add the record.

If this fails, the e_insert_excep handler is triggered.

Recovery Process:

select max(deptno) + 1 into v_depto from dept; retrieves the current maximum deptno and increments it by 1.

A new insert statement uses v_depto to save the record in the table.

Example Execution

Let’s look at how the provided code functions in practice.

A new table x is created with department entries.

The erroneous insert is attempted with a NULL for deptno.

Upon catching the exception, the code selects the maximum department number, inserts the new record, and commits the changes.

After running the above block, the final entries in the table reflect the successful insertion:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Handling insert exceptions in PL/SQL using the method outlined above ensures data integrity while allowing for seamless operations within your applications. Always remember to implement appropriate error management strategies for robust and reliable code.

By following this guide, you can confidently manage insert failures and keep your database operations running smoothly.