Oracle Master Training • 60,000+ Students Worldwide
A free video tutorial from Oracle Master Training • 60,000+ Students Worldwide
Oracle Architect & Best Selling Instructor
4.4 instructor rating • 7 courses • 63,877 students

Lecture description

In this lecture you will learn how to raise an exception to the caller application with using pl sql raise_application_error procedure

Learn more from the full course

The Complete PL/SQL Bootcamp : "Beginner to Advanced PL/SQL"

A Full-Real Guide to Make You a Real PL/SQL Developer! Also covers "Oracle 1Z0-144 and 1Z0-148" PL SQL Exams Completely!

20:25:57 of on-demand video • Updated June 2020

  • Pass the Oracle 1Z0-144 : Program with PL/SQL Certification Exam - on your first try!
  • Pass the Oracle 1Z0-148 : Advanced PL/SQL Certification Exam - on your first try!
  • Anything You Need for Coding PL/SQL From Beginner Level to Advanced Level
  • Every Subject is Explained from the Easiest to the Most Advanced.
  • Real-World Examples for Every Section
  • Some Advanced SQL Techniques
  • Using SQL Developer Efficiently for PL/SQL
  • Improving Performance & Security of Your Codes
  • PL/SQL Architecture
  • Creating Dynamic Queries
  • Use PL/SQL Like Object Oriented Programming.
  • PL/SQL Tuning
English [Intro] Hi. In this lecture, you will learn how to use the raise_application_error procedure. In previous lectures, we learned how to raise an exception in our block and learned how to handle it. And said that, sometimes we need user-defined exceptions to handle some business exceptions. But, they are not defined in the database and they are not an error for the database server. So they don’t have any error codes. But in user-defined exceptions, we defined, trapped and handled the error in our blocks. We know that they are not known out of the blocks. But most of the times, your code will work with different subprograms or applications. So you will need to inform these, about your business errors. For example, if you are using Oracle Forms or Java, and you call a procedure or function including a block of code, you need to be informed about the business exceptions. We know that we cannot raise a user-defined exception out of our block. We get an error if we do that. But sometimes, we want to raise an exception to the caller application to stop the execution. To do that, we use the raise_application_error procedure. Raise_application_error procedure raises the error to the caller subprogram or application. With this procedure, we will inform the caller about our business exceptions. Let’s check how to use it and continue talking on that. We simply write raise_application_error and inside of the parentheses, 2 or 3 parameters. The third one is optional. The first parameter is the error code and the second one is the error message. error. We raised an application to the caller with an exception called in a specific message. The third problem there is the boolean parameter. This is for the error stack. So what is the error stack up to now. We used on the one in on this block. So only one exception is raised from our blocks most of the time. But when you learn how to use the subprograms multiple subprograms we'll call each other. So one exception will cause multiple exceptions to raise from bottom to up all these error messages and calls are stored in error state and print them all then the whole execution finishes actually. Sometimes you will see many error codes and messages in the script up and these are stored in our stack and putting that all in one step as soon as the program execution is terminated. This third parameter is used for this. If you select true for this parameter your area that you assign in reciprocation appreciate your error to the error stack. And with this way you will see all the other errors that occur. But if you select force by default it is false. This time all the previous errors are deleted from this day and only this error is stored in it. So when the Vergon finished you will see only that error message. We will make an example about that. All right then you raise the application error it will cause your program to stop the execution. So you need to handle that in your application with and then others have an exception handler. Now let's explain the error number and the error message since this error number is specif for our business it must be different than the resort coastal areas to provide that Oracle lets us to specify a number between minus 20000 and up to twenty one thousand two hundred and thousand is not good. So we need to write a number between this interval. This called will be raised to the application as the error code of the exception. The message is a specific character string. After two kilobytes long this will be airmass of our exception. All right. Now let's make an example and see how to use that procedure. Now this is our previous user defined example. We know that we need to handle that error inside of our block. We cannot raise it to the caller application. Now instead of raising our exception let's use the raise application error procedure. Let's comment Our raise first no let's raise our own message with a raised application or procedure raise application. And inside parent as the first one is our record let's say minus 2 0 2 4 3. For example therefore the error message. Let's say the salary of the selected employee is too high. Now let's run it as can be seen in the script output our record and our error message are printed. If this block will be called by an application this message would be raised to that application. Now we used our razor application our procedure inside of the big block. We could use it inside of the exception Section 2 with. This way we handle what you want first then raise for the rest to the caller program. So let's see that let me quote it and uncommon here and paste all these application in here and write it now let's check the outputs. The first one is our message. The second line is a message for showing at which line did this happened. The last text comes from RDBMS output. Now let's see what we will see when we use the third parameter. We know that then we write nothing. It means force let's write force First let's clear up and run our code as we can see the output is the same. So let's run through them. And the Right now we see one more error message on look. P.S. UGL user defined the exception not we that. Then we raise our position our with force or now parameter it replaces all the other error messages. So we can ride through here to see all the other error messages in here. We see that when we raise that error since we could not handle the user defined error it is also raised. So they both inserted the error stack and printed as soon as the block is terminated. This is not a useful subject sometimes. But since the other errors are cured because of the first one they will be held then you handle the first plan. So you will not need that at where most of their time. They just think if we write a result or other error code we will get an error. Let me show you 0 1 4 0 3. We know that this error number is reserved for an audit of on really finder. Now let's run it as we tansy. It says use an error number in the range of minus 20000 and minus 21000. Even if you're right and I'm truly finder of code it will have an error or two. All right. Now we know how to use razor application or procedure. So this is the end of this lecture. See you in the next one.