Skip to main content

PL/SQL Exceptions

In PL/SQL, exceptions are used to handle errors and abnormal conditions that may arise during the execution of a program.

When an error occurs, an exception is raised, and the program can handle it using exception handling blocks.

You can declare your own exceptions or use predefined exceptions provided by Oracle.

DECLARE

   my_exception EXCEPTION;

BEGIN

   -- Code that may raise an exception

   RAISE my_exception;

EXCEPTION

   WHEN my_exception THEN

      -- Code to handle the exception

      DBMS_OUTPUT.PUT_LINE('My custom exception was raised.’);

END;

 




SYSTEM EXCEPTION

These exceptions are predefined by Oracle and are raised automatically when certain error conditions occur during program execution.

NO_DATA_FOUND: Raised when a SELECT statement returns no rows.

TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row.

ZERO_DIVIDE: Raised when an attempt is made to divide by zero.

INVALID_NUMBER: Raised when conversion of a character string to a number fails.

BEGIN

   SELECT 1/0 INTO result FROM dual;

EXCEPTION

   WHEN ZERO_DIVIDE THEN

      DBMS_OUTPUT.PUT_LINE('Division by zero error occurred.');

END;

EXCEPTION:

This keyword marks the beginning of the exception handling part of the PL/SQL block.

WHEN ZERO_DIVIDE THEN:

This line specifies the exception handler for the ZERO_DIVIDE exception. If a ZERO_DIVIDE exception is raised, the code inside this block will be executed.

 

USER DEFINED EXCEPTION

These exceptions are defined by the programmer to handle specific error conditions that are not covered by system exceptions. They provide a way to customize error handling for application-specific scenarios.

RAISE Statement: You can explicitly raise an exception using the RAISE statement.

DECLARE

   my_exception EXCEPTION;

BEGIN

   IF condition THEN

      RAISE my_exception;

   END IF;

EXCEPTION

   WHEN my_exception THEN

      DBMS_OUTPUT.PUT_LINE('My custom exception was raised.');

END;

 

RAISE my_exception;:

This statement explicitly raises the user-defined exception my_exception.

When this statement is executed, the normal flow of the PL/SQL block is interrupted, and control is transferred to the exception handling part of the block.

 

EXCEPTION:

This keyword marks the beginning of the exception handling part of the PL/SQL block.

 

WHEN my_exception THEN:

This line specifies the exception handler for the my_exception exception.

If the my_exception exception is raised, the code inside this block will be executed.

 

RAISE_APPLICATION_ERROR Function: You can use the RAISE_APPLICATION_ERROR function to raise a user-defined exception with a custom error message and error code.

BEGIN

   IF condition THEN

      RAISE_APPLICATION_ERROR(-20001, 'Custom error message');

   END IF;

END;

RAISE_APPLICATION_ERROR(-20001, 'Custom error message');:

This function is used to raise a user-defined exception.

-20001 is a custom error code. Oracle allows user-defined error codes in the range of -20000 to -20999.

'Custom error message' is the custom error message that will be displayed when the exception is raised.

When this function is called, it stops the normal execution of the PL/SQL block and raises an exception with the specified error code and message.

 

 

 

 

 

 

 

 

 

 

Comments

Post a Comment

Popular posts from this blog

TechUplift: Elevating Your Expertise in Every Click

  Unlock the potential of data with SQL Fundamental: Master querying, managing, and manipulating databases effortlessly. Empower your database mastery with PL/SQL: Unleash the full potential of Oracle databases through advanced programming and optimization. Unlock the Potential of Programming for Innovation and Efficiency.  Transform raw data into actionable insights effortlessly. Empower Your Data Strategy with Power Dataware: Unleash the Potential of Data for Strategic Insights and Decision Making.
Post a Comment
Read more

Relationships between tables

In Power BI, relationships between tables are essential for creating accurate and insightful reports. These relationships define how data from different tables interact with each other when performing analyses or creating visualizations. Here's a detailed overview of how relationships between tables work in Power BI: Types of Relationships: One-to-one (1:1):   This is the most common type of relationship in Power BI. It signifies that one record in a table can have multiple related records in another table. For example, each customer can have multiple orders. Many-to-One (N:1):   This relationship type is essentially the reverse of a one-to-many relationship. Many records in one table can correspond to one record in another table. For instance, multiple orders belong to one customer. One-to-Many (1:N):   Power BI doesn't support direct one-to-many relationships.  One record in table can correspond to many records in another table.  Many-to-Many (N:N):  ...
Post a Comment
Read more

SQL Fundamentals

SQL, or Structured Query Language, is the go-to language for managing relational databases. It allows users to interact with databases to retrieve, manipulate, and control data efficiently. SQL provides a standardized way to define database structures, perform data operations, and ensure data integrity. From querying data to managing access and transactions, SQL is a fundamental tool for anyone working with databases. 1. Basics of SQL Introduction : SQL (Structured Query Language) is used for managing and manipulating relational databases. SQL Syntax : Basic structure of SQL statements (e.g., SELECT, INSERT, UPDATE, DELETE). Data Types : Different types of data that can be stored (e.g., INTEGER, VARCHAR, DATE). 2. SQL Commands DDL (Data Definition Language) : CREATE TABLE : Define new tables. ALTER TABLE : Modify existing tables. DROP TABLE : Delete tables. DML (Data Manipulation Language) : INSERT : Add new records. UPDATE : Modify existing records. DELETE : Remove records. DQL (Da...
Post a Comment
Read more