Ensuring your database code is efficient and working as designed is an essential part of the development life cycle. In this video, I’ll show how to utilize the database debugger to step through your code to ensure efficiency and correctness. In this example, we’ll be working with an Oracle stored procedure that returns a list of clients for a specific broker.

I’m using Rapid SQL which provides a debugging interface that is built right into the application. To get started, it’s as easy as finding the stored procedure and selecting Debug from the context menu. I’m then prompted for input variables and then placed within the debugging interface.

Here we can step into our code, step over a calling stored procedure, step out of a stored procedure, as well as run to the specific point where we placed our cursor. At the bottom part of the screen we’re looking at all of the variables that are defined and their values. A Watch window provides us the ability to watch specific variables as well as change the values of the variables. In the right-hand pane we can see the call stack of the stored procedure as well as any dependencies. Hopping back, let’s scroll down to the place in my code that is of interest.

At this point we’ll run the cursor and we’re stopped here at this line. We’ve able to see that the overall average price is $51.08 while the specific price for this transaction is $67. If I need to change the value of these variables to ensure correctness in my code I can either manipulate the data in my database or simply find the Watch window of the specific variable in mind, change the value, and go back into the code, stepping through. So again we can see how our code reacts based on different values of data using the database debugger to ensure correctness and efficiency within our stored procedures. Finally, we can execute the stored procedure to run it to the end, thus presented with the results of our stored procedure.

As a database developer you have many responsibilities, but running correct and efficient code is one of the most important, and having the right tool to do the job is key. For over 20 years, database developers have been relying on Rapid SQL to build better SQL.

Visit idera.com and start your trial of Rapid SQL today!