What's up Internet? I am Manish from RebellionRider.com and today's PL/SQL tutorial is going to
be about how to declare user-define exception using PRAGMA EXCEPTION INIT function in Oracle
Database. In the last PL/SQL tutorial we learnt the
way of declaring user-define exception using RAISE APPLICATION ERROR procedure. Though
this is also a very good way of declaring user define exception but there is a problem
with this method. Here we cannot name the exception.
In PL/SQL we handle all the exceptions which has no name in 'Others' exception handler
and that causes confusion, especially when we are working on a project which is huge
and have multiple user define exceptions. We can easily overcome this problem by using
PRAGMA EXCEPTION INIT function. So what is this PRAGMA EXCEPTION INIT function?
Using the pragma EXCEPTION_INIT we can associate an exception name with an Oracle error number. And
we can use that name in designing the exception handler for the error.
Let's do the example and see how to declare user-define exception using Pragma Exception
Init function in Oracle Database. The demonstrative example is going to remain
the same as the one which we did in the last tutorial except for a few minute changes.
So let's do it. DECLARE
ex_age EXCEPTION; age NUMBER := 17;
This time in the declaration section we have two variables in which first variable is an
Exception variable. This exception variable ex_age will now serve as the user-define exception
and become its name. Second variable is a simple variable with
datatype number. I also initialized this variable with value 17 which is unlike the previous
tutorial where we initialized it by accepting the input value from the user using Accept
command. If you don't know what I am talking about,
then go ahead and watch the previous tutorial. But for now let's move ahead.
After declaring all the variables in the declaration section, we will have the PRAGMA EXCEPTION
INIT statement. PRAGMA EXCEPTION_INIT(ex_age, -20008);
Here we have the exception init statement, before explaining it let me quickly show you
its syntax. PRAGMA EXCEPTION_INIT (exception_name, error_number);
Here we are. You first write the keyword PRAGMA followed
by the function name which is Exception_Init and then inside the parenthesis you pass two
parameters. In which first one is the exception name, which will be the variable name of exception
datatype and second is the error number. You can put any number as error number in
parameter but if you want to use PRAGMA EXCEPTION INIT in conjunction with RAISE APPLICATION
ERROR procedure, then you have to make sure that the number is in between -20,000 to -20,999.
Now let's take a look at our statement. Here as the first parameter I passed the name
of our exception variable and as error number I passed -20008 same as in the last tutorial.
That completes our declaration section. Now let's go ahead and write the execution section.
BEGIN IF age < 18 THEN
RAISE_APPLICATION_ERROR(-20008, 'You should be 18 or above for the DRINK!');
END IF;
DBMS_OUTPUT.PUT_LINE('Sure, What would you like to have?');
EXCEPTION WHEN ex_age THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END; /
Here we have the execution section. As you can see it is pretty much same as the one
we coded in the last tutorial. For example in the beginning we raised the exception using
raise application error procedure. As a parameter of this procedure I first passed the number
which is -20,008 This number should match the one you specified
in the pragma exception init function in the declaration section. Though it is not mandatory
but for the sake of consistency and to avoid any confusions, try to match this number with
the one you have passed in your EXCEPTION_INIT function. This way you can easily find that
section of your program which is causing the error in your project.
Second parameter is the error string which will be displayed back to the user along with
the error number. Try to make this error string brief yet descriptive so that the normal user
can understand what the error is all about. And we put this raise statement inside the
'If condition' this way we are making sure that the exception occurs only if the
age of the user is below 18. Right after the raise exception statement
we have a simple DBMS output statement. You can put any number of executable statements
here but for the demonstration I am good with this single statement. This statement will
execute only when the age of the user is 18 or above otherwise the program control will
go to the exception handler. Which is the next part of this execution section.
Have you noticed something different here? No, not yet? Take another look carefully.
Right, exactly… Though the rest is same but unlike the last
tutorial today exception handler has a name of exception, which is ex_age.
So what does that mean? As soon as compiler gets to know that the age of the user is below
18 it raises the exception, when the exception is raised the compiler starts looking for
a matching exception handler. If the exception does not have any name the
compiler looks for the exception handler with the name 'Others' which is the case in
the last tutorial. But if the exception has a name then the compiler looks for the handler
with the matching name. That is exactly the case in today's PL/SQL tutorial.
We raise an exception with the number -20,008 and then map a name ex_age over that number
thereafter using that name we design an exception handler which is printing the error stack
on the display screen. Isn't it simple! Let's compile and see
what happens. As the user age is 17 which is less than 18
thus we got the error. I will be dropping the link of the blog on
the same topic in description of the video. There I've explained the advantage of naming
an exception and answered the question of why we use exception inti function with raise
application error procedure & not with raise statement and what is this PRAGMA keyword
all about. Wait video is not over yet, before winding
up this tutorial I have a task for you. Here I hardwired the value of user age which
is 17. Now you have to write the same example except your example should take the user age
input from the user. The input pop up box must have a customized message not the default
one. Also if you want to introduce some other changes in the example then go ahead & do
that. You have to comment your source code in the
comment section down below. You can also send the screenshot of the pop up input box with
customized message on my twitter @ RebellionRider. So many of you guys have been asking what
equipment and software or devices I use to create these videos. If this video gets 1000
likes then I will make a video explaining my process of making YouTube videos along
with all the devices I use. Thanks for watching this tutorial on how to
declare user define exception using pragma exception init function in oracle database.
Don't forget to Like, Share and Subscribe. Thanks this is Manish from RebellionRider.com
For more infomation >> ВСПЫШ И ЧУДО МАШИНКИ Новые серии от Tyomka КРУШИЛА НА ШАРЕ Мультики про машинки для детей 2017 BLAZE - Duration: 3:37. 
For more infomation >> Desi Bhabi Romance भाविन सबकुछ दिखादिया - desi romance video - Duration: 0:28. 
For more infomation >> 러시아 여자들이 고른 최고의 남자 패션 / 이상형 월드컵 /한글 자막 - Duration: 8:33. 
Không có nhận xét nào:
Đăng nhận xét