Handling Compilation Errors

Intellezy Trainers
A free video tutorial from Intellezy Trainers
Computer Training Specialists
4.4 instructor rating • 198 courses • 152,691 students

Learn more from the full course

Oracle PL/SQL Fundamentals vol. I & II

A complete introduction to PL/SQL database programming language

19:59:18 of on-demand video • Updated May 2020

  • Over 90 lectures and 20 hours of content
  • Prepare your enviornment
  • Introducing Database Program Units
  • Creating and Maintaining Packages
  • Using System-Supplied Packages
  • Creating and Maintaining Database Triggers
  • Implementing System Event Triggers
  • Work with Oracle database tools
  • Understand and work work language features
  • Work with DECLARE, BEGIN & EXCEPTION clause
  • Understand and use nested blocks
  • Use explicit cursors
English [Auto] Compilation errors or something that you're going to have to deal with rarely are you going to be able to write an application and it's going to compile perfectly the first time when you compile an application within Oracle it does things a little differently and it's actually kind of neat. I really like this quite a bit to be quite honest. So when you compile an application if it is successful the program unit is ready for execution. It gets added into the database and it says hey I'm valent I can run right now. And that's a good thing. But if you have an error if the compilation fail error fails due to errors it still gets stored to the database. So Oracle doesn't just say OK I don't want this thing it's it's totally wrong and I want nothing to do with it. No on the contrary Oracle says OK I'll store it because I don't want you to have to rewrite everything or keep track of it. But I'm going to put it in an invalid status. So you can't run it until it's all fixed and you will get an error or something like this where it says warning execution completed with warning and it'll say function salary valid comp.. So it'll tell you it compiled what it's going to tell you that there were some problems with it and you will even be able to see this when you're inside of the sequel developer environment. I'm going to show you this here in a little bit. Now when it when it fails it automatically places those errors inside of a view called user errors. And so now since it's inside of this view you have the ability to generate a report or you can even query that view saying hey I want to select all from user errors and it'll show you all the errors for all of your applications for any program that has an error. What's also neat is that if you fix that. All of those errors will disappear out of the user errors view. But here's an example of errors report right here where you can see what line it is. It tells you a lot of detail and also test give you a lot of stuff that you probably don't care about. But these are all of the errors that Oracle knows about. For each object that gets compiled. Now when you want to generate an error you can use that show errors command but then you define what object you want to work with. So you can say show errors and I want to see only procedure errors. So if you stopped right there you'd be able to see all your procedure errors. But if you also put in the name you can see all of the errors associated to that object. And it gives you a means of going back and taking a look at it. Now this is handy. Don't get me wrong but I'm not going to go to the user errors view to find out what problems I have with a particular program. I'm just going to open it back up again recompile it take a look at it and I can use the error log inside of sequel developer and I can double click on those errors and it will take me to that location. It's very handy. I'm going to show you both ways. And also one thing I want to point out to you is that the show errors command can be fired from simple plus. Or the sequel developer environment it does not matter. So let's take a look at it. Instead of sequel developer I've already opened up the project called error Proc. and this thing is riddled with errors. There are all kinds of problems with this thing so it will not compile successfully. To see this. Open it up and just press F5 F5 and all in here need to click on it. Press 5 and it says procedure error salary comp. But errors. And it says check the compiler log so I can check the compile a log using the show airs command. But another thing that's really neat about sequent developer is down here at the bottom. Here's my compiler log and it says error 20 combo. 14. If I double click on this it automatically takes me to that line so says here's the problem and my problem with one thing you need to remember is that when you double click on something it may put you on that line but it may put you one line below. So keep that in mind as well and it all depends if you have. You always get that if you have the show set server output on and then you have a couple of lines. It's always going to be off a little bit. But if I double click on this the problem is not in the res percent. Actually it is. I have a couple of errors right there. I have two errors. One is employee percent. Does it have an underscore and the other one is percent score too high. There is no underscore there as well. So by leaving this out it looks like it's got two parameters and it has no idea what I'm talking about. Now as you fix each one of these errors the next time you compile that log will get smaller and smaller and smaller. But before I do that I want to take a look at this error log that is stored by work. So if I go over to my error report I have two ways of doing this. I have my show errors and I want to see the show errors per procedure error salary. So if I use that one I'm going to highlight it press 5 yes I want to use system and here are all of those errors that I can scroll right through there. So it looks pretty much like what I showed you in that screenshot. And there are a bunch of them. But the other thing I can do remember all of those errors are stored in a view called user errors. So if I want to see that I can use this select committee and I can just run a query select start from user errors press 5 7 rows are selected. And here it shows each one of these. Now the thing is is notice that it wraps. That's the one drawback to this. If you use the View it's going to wrap these things so it says oh well this one is very wide so it'll show the first set and then it's going to go down the next line and it's going to show the ancillary things after the fact. So it depends on how wide your screen is and how much you want to work with it. If you just want the show error report there you have it. So it shows each one of those errors. Now the other thing I want to point out to you is that this command or this this program that we just created is in the database and you can see this what I'm going to do is I'm going to just zoom in right over here. So here's my raise salary. So I see Ray's salary and I see Ray's salary valid and if I select procedure and like refresh now I have error or salary error or salary is the name of that that procedure it didn't sequel or Oracle did not put that error the error or name in there. But what it did do. Notice that there is a red X that X is telling me that this this procedure is not valid. It cannot be executed at this time because there are errors. So this is something that you want to pay attention to when you see a red X. It tells you that there's something wrong with this particular procedure or function. And if you want to see it all you have to do so is I know error salary is the problem. You just go over here. They show errors procedure since it's in the procedures list error salary semi-colon and it's going to show you all of those. So it's a very neat feature. How Oracle tracks all this stuff for you. I highly recommend you use it.