Hi. In this lesson, we talk about how to handle exceptions in PL SQL. What is an exception first exception is a runtime error. As you run the program, it might run into errors. For example, you want to update a row, but the updation failed. You want to insert a row, but maybe you're inserting a duplicate value in a unique column are you're trying to retrieve the data, but the data you want to retrieve is not available.

In all these cases, pl SQL is going to throw exceptions. And these exceptions are to be handled. And if you don't handle the exceptions, your program is going to terminate abruptly and abrupt termination is to be avoided at any time. cost, because no program should be abruptly terminated. So as a programmer, you must anticipate what are the exceptions your program is likely to raise and be ready to handle them. And that is the reason why we have the third part of our blog called exception handler.

And the syntax of exception handler is simple. You have to mention the exception you're ready to handle and the action to be taken. And you can handle different exceptions in different ways. And you can also handle all the exceptions in one way. And that's what the last part of this exception handling syntax shows, when others that means when any other exception other than what we mentioned, is occurring. Then we want to take this action.

Let's look As an example, this is where I want to retrieve salary of employee called a mate. If the salary of employee um it is found, we get the value copied into variable v salary. But what if there is no employ with the name Ahmed found? PL SQL treats this as an error, it's going to raise an error called no data found. No data found error occurs when the Select command cannot retrieve any data. And if by any chance you have more than one employ with the same first name a mate then also it is a problem.

Then select command is going to retrieve multiple rows, but we cannot copy multiple rows into one variable. So In the context of PL SQL, even this is considered to be an exception. And the exception name is too many rows. So the simple select command is having three possibilities. It might be retrieving one row, it might be retrieving more than one row, or it might retrieve none. So if it retrieves nothing, this is going to raise no data found exception.

And if it retrieves more than one row, then it's going to be too many rows exception. But if it retrieves only one row, the salary is copied into the variable and we are going to proceed to the next statement. And eventually our block is terminated successfully. Let us see that. So here is my program. I'm going to execute this session And if the Select a successful, we go to this DBMS OUTPUT to display the salary for it.

But if I made these not found, we go to no data found. But by any chance if you have more than one employee with the first name I made you go to too many roles. So let's go and run this. And if I run this program, right now, it is saying the salary of Amethyst 16 00. That means we do have an employee with the name Ahmed, but let us just change this to something like Amit one. Obviously there is no employee with this name.

So go ahead and run and you will see an error and the message is simple. Let's clear that run again. And you see the message says employ Ahmed not found. So when you're looking for something with this name, This is raising an exception that will take you to no data found, which is a predefined exception. And we have a set of exceptions called as predefined exceptions. We'll see List of that little later.

And if this is the exception that is raised, we catch here and we take this action. But if we have an employee, then we go to the next statement execute that and after that the block is terminated. But let us say I'm looking for the salary of employees with first name James, but they might be multiple employees with first name James. It's going try this. And there you are. More than one employee found with the name, of course not a myth, because we are testing James.

But the point is, this is raising an exception to many roles, and we catch that here and we are taking this action. Now, what if you want to handle only one exception and take some common action for the rest of the exceptions, then we can go for when others when you say when others you are saying all the exceptions other than this should be handled by this. And then we would like to take some common action. So, I don't know what is the exact problem. So, we can find it out by using two functions, SQL code is going to give you the error code. And then we have something like SQL AR M. This is the error message.

Well, every error message is also associated with the code. So we get the code here, we get the message there. And these two are standard functions. So let's go and execute this, because the exception is too many rows selected. And this time that is caught here, this is where we handle it. Let's go run it and it is going to display the details.

And there you can see, let's just clear all run again and it is saying the error is 1422. And all these codes are generally a negative number. So 1422 is the error number and this is the error message. So in case if you want to handle the remaining errors in one, when block using when others it is possible. But if you want to take any specific action, then you need to do separately for each one. So, this is about basic exception handling.

And moving back to this, this is about SQL code and SQL error message this to return the appropriate details. Now, these are the predefined exceptions, we already used no data found too many roles, but we also have some other predefined exceptions like do well on index. If you ever tried to insert a duplicate value into a column that is supposed to be unique, you get this. Similarly, if you ever gave an invalid number where a valid number is expected, you get invalid number. Value error occurs whenever there is basically a size error. For example, there is a variable which is can accommodate five characters but you're trying to assign more than five characters.

And zero divide is universal. Anytime you try to divide a number by zero, you get this. So these are all predefined exceptions. In addition to that, you can also create your own exceptions. We'll see that shortly. But interestingly, no data found is not raised in two specific cases.

Most of the beginners thing are not data found is raised in all cases, but it's not so. So number one, if the Select command is using any group function like this, select count of star etc. Even though no row is selected, no data found exception is not raised. Instead, the count is going to be zero if no employ is found, With a salary greater than 1500, then we get count zero. In case if there are employees, we get their proper headcount. So it means select command with group function never raises no data found exception.

While in case if you're using functions like sum and average, instead of getting zero, you get null value. So other group functions like main Max and all return null if no row is selected, and then coming to update and delete. Even those two commands Do not raise no data found. So I'm trying to change salary for employed triple one. But if employ triple one is not found, you're not going to get any exception. It'll just go to the next statement.

You have to be aware of this because if you expect the no data found to be raised here, you are wrong. But if you're wondering how do I get to know whether this update really succeeded? We will see more about that later in the next lesson when we talk about implicit cursors. But for now, all that you need to know is there is no no data found exception in these two cases. Now, we can create user defined exceptions. In addition to predefined exceptions.

There are three simple steps number one declared the exception. All that you need to do is give a meaningful name and give the keyboard exception number to raise the exception when you feel there is an error. And it's user defined exception. So you decide when to raise it. Unlike predefined exceptions, which are automatically raised by PL SQL and the law Point is where you handle the exception. So you have to give exception handler and handle the exception.

And this is just like how you handle any predefined exception. So there are cases where you might want to create your own exceptions, raise them to indicate that something is wrong in the code. Now, when we talk about exceptions, we also need to know the impact of nested blocks on exception handling. To start with, a block can be nested in another block. And here, this is the nested block. The whole thing is part of another block.

So PL SQL starts execution of code here in the outer block. Then it goes to in our blog, it'll execute the statements. If all is well alien Then jump to this and continue with the code given in the outer block. But what is the impact of exceptions in this context? What if there is an exception in in our block? What if this is raising an exception, and if you handle the exception in the inner block, then the exception is handle there and then we come out of fate as if there was no error.

That means, we come out of inner block and resume the execution of outer block. That means you can handle the exception and then continue with the outer block as if there was no problem. But if the exception is not handled in the inner block, then the same exception will be propagated to outer blocks exception handler. This is important If an exception is not handled here in the inner block, the exception is considered to be propagated. So it is propagated to outer block. So, outer block has to handle it.

And if outer block is also not handling it By any chance, it is further propagated to what is called as host. That means the very environment from where you are running this program. And the host can be something like your SQL developer or it can be the database. And we will see later how to use PL SQL to create database program units like procedures, functions and triggers. And then you understand that host is database not any program, not any tool like SQL Developer. So, this is how our blocks can be nested.

And if you see the scenario Yo, here it is, I'm raising exception a in the inner block and it is handle there. So, we continue with the outer block. And the second scenario, you raise exception a not being handled in the inner block, so it is propagated to outer block and outer block has to handle it. But in between the inner block and outer block whatever statements we have, they are all escaped because the exception is not handled here. So, from there it will jump to the exception handler of outer block. So, it's considered to be a failure of this inner block.

As inner block fails, we don't continue with the statement and we want to handle the exception in the outer block. This is called propagation to outer block and the next one is propagation to host. So it's not handled in the inner block. It's not handled in the outer block and the exception will be propagated to host that means the environment from where your program is executed. So those are the scenarios you should be familiar with. And the last of the scenarios is, it is also possible for you to handle an exception in inner block like this.

And then after handling the exception, you may want the same exception to be handled in the outer block for some reasons. So then you need to use a race statement. Simple race statement is going to reraise the same exception. That means in this context raise is going to raise out of stock exception, and that will take me from here to outer block. And there I handle the exception. Again, this is called as re raising exception.

That means you handled it in the inner block, but you're not done with that you want the same exception to be raised. So that outer block again handles it. Well, of course, the exception you're raising should be visible both to inner block and outer block. So if you're planning to handle it in both the blocks, you must declare it in the outer block so that it is available to both. So that is called raising an exception. It is possible and there are cases where you might have to do it, though not something you do every time.

These are scenarios I want you to know so that you can use them when the need arises. Then, in some cases, you may want your program to be explicitly terminated abruptly. So I want to raise an error and stop my program, I don't need to create any exception or raise an exception, I can use something called RAISE APPLICATION ERROR. And when we use that, we need to give two different parameters, the error number, and you're supposed to use only the numbers in this range, because the rest of the numbers are assigned to predefined errors. This is a kind of user defined error. And then what is the message that you want to associate with the error?

So we use this a lot in the coming lessons when we talk about procedures, triggers and all because when you raise an application error, you are explicitly terminating your PL SQL block, and you're terminating a unsuccessfully. So that means whenever you RAISE APPLICATION ERROR, the block is terminated and it's considered to be unsuccessful. And that brings us to this. So when a PL SQL block is considered to be successful, well if no exception is raised, and if an exception is raised, but it is able to handle it. So if no exception is raised, or there is an exception, but the block is able to handle it successfully, in these two cases, we consider the block to be successful. The success of the block or the exit status of the block is a very important factor.

So you need to know whether the block is succeeding or failing. Well, when the block is considered to be unsuccessful when it exits with unhandled exception. If the exception is not handled in the block, it's considered to be unsuccessful. And also when it is explicitly raising APPLICATION ERROR using RAISE APPLICATION ERROR, even then the block is considered to be unsuccessful. This is a very important point. And we'll talk a lot about this or use a lot of this in the coming lessons.

But for now, I just want you to know that every PL SQL block is having exit status, which is either success or failure. And those are the cases for success. These are the cases for failure. In this lesson, we understood that a PL SQL block can raise exceptions, otherwise known as runtime errors and exceptions might be predefined are user defined. If user defined exception, you have to define it and you have to write it and both predefined and user defined you need to handle them in Exception Handling portion. The third part of your block and not data found is not raised into cases though it seems that it is a candidate for those cases also.

I mean, update might not update any row at all, then you might think it is going to raise no data found no it's not so and also solid command with any group function is not going to raise no data found. And then we can have a block within block we discussed about nested blocks and exception propagation in the context of nested blocks. Of course you can have multiple nested blocks, not necessarily one, and we understood you can re raise an exception. And we understood you can RAISE APPLICATION ERROR and stop your PL SQL block unsuccessfully. Every PL SQL block is either successful or unsuccessful, we have understood the cases in which it's considered to be successful, and the cases in which it is not successful. So that's about exception handling, be aware of the possible exceptions raised and be ready to handle them.

Otherwise your program becomes very fragile, and it breaks whenever there is an exception, but your programs must be robust. So make sure you handle exceptions. That's for now. See you in the next lesson.