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

Performance Optimization

Performance optimization in SQL is crucial for ensuring that your database queries run efficiently, especially as the size and complexity of your data grow. Here are several strategies and techniques to optimize SQL performance: Indexing Create Indexes : Primary Key and Unique Indexes : These are automatically indexed. Ensure that your tables have primary keys and unique constraints where applicable. Foreign Keys : Index foreign key columns to speed up join operations. Composite Indexes : Use these when queries filter on multiple columns. The order of columns in the index should match the order in the query conditions. Avoid Over-Indexing:  Too many indexes can slow down write operations (INSERT, UPDATE, DELETE). Only index columns that are frequently used in WHERE clauses, JOIN conditions, and as sorting keys. Query Optimization Use SELECT Statements Efficiently : SELECT Only Necessary Columns : Avoid using SELECT * ; specify only ...
Post a Comment
Read more

DAX UPPER Function

The DAX UPPER function in Power BI is used to convert all characters in a text string to uppercase. This function is useful for standardizing text data, ensuring consistency in text values, and performing case-insensitive comparisons. Syntax: UPPER(<text>) <text>: The text string that you want to convert to uppercase. Purpose: The UPPER function helps ensure that text data is consistently formatted in uppercase. This can be essential for tasks like data cleaning, preparing text for comparisons, and ensuring uniformity in text-based fields. E xample: Suppose you have a table named "Customers" with a column "Name" that contains names in mixed case. You want to create a new column that shows all names in uppercase. UppercaseName = UPPER(Customers[Name]) Example Scenario: Assume you have the following "Customers" table: You can use the UPPER function as follows: Using the UPPER function, you can convert all names to uppercase: UppercaseName = ...
Post a Comment
Read more

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