Debugging in cloud or datacenter

In version 20.2 came a lot of nice features with among other things improvements to ORDS integration and more Liquibase features. But by far the one I liked the most was debugging. “but we’ve had debugging forever”. True, but if your database is in the cloud or in a datacenter where NAT or such things are used, you have not been able to use it as it requires traffic initiated from the database server to your PC and that is often not possible with the pre 20.2 version of debugging. The database cannot route to your PC when all traffic from workstations are multiplexed to a single address. For a datacenter your PCs address may well be something very many customers has for their employees machines.

What is needed is a debug session that can be initiated by yoru machine and managed in that session. That has not been possible before.

With SQL Developer 20.2 you can initiate it from SQL Developer, then the debug session is created as usual but the connection to it is then done by a session being initiated from SQL Developer to that session i the database. After that it all works just as the old way did when you had the networking access to use it.

Enough about how it works, how do you get SQL Developer to take advantage of this feature?

You obviously need to upgrade your SQL Developer if you have an earlier version.

Now with a current version of SQL Developer, let’s head to preferences. (Tools -> Preferences)

Note the option to select between DBMS_DEBUG_JDWP and DBMS_DEBUG. You want to select DBMS_DEBUG.

With that change you can now klick on the bug to start debugging .

But before it lets you actually get into the code and step through it, you need to compile it for debug. Without that no breakpoints or other debugging features will work.

With that compile completed, you can just press F5 on a line or right click in the margin to set a breakpoint so there is at least one place for the code to stop at.

If you press the bug-icon now, you’ll get the following dialog to adjust both the call and what you want it to do afterwards. I usually just modify values for the input parameters and possibly if I want anything to be printed afterwards.

In this case I’ve modified the value of the parameter from NULL to 12. Press OK and next up you’ll be in the code stopped at your first breakpoint.

In the below screen capture I’ve stepped down a few lines and also added on watch for the input parameter and one for the variable.

It is also possible to change the value of parameters to see the effect. Or to do any other things you usually do in a debugger in an IDE. To learn more about that, play around and look for the things you would want to take a look at the documentation.

SQLcl is turning into a Swiss knife

ather it has been happening for a long time. I just did not pay attention of just how useful it had become. The tool has long been the preferred TUI/CUI (Text-based user interface / Command line User Interface) for SQL and PL/SQL. Not only has the tool added a lot of nice features, just see my last blog post for an example, it also allows for scripting to make it even more compelling.

Today it is also how you evolve a schema incrementally. Think of it as how you add changes to a file to git. Every change is checked in as a delta for what was already in place. With the Liquibase features SQLcl is now the tool to in all you databases evolve schemas and have the tool keep track of which scripts has been run and which needs to be run to bring the schema up to the current version. It also adds a lot of other nice features that has come with the ORacle implementation. That is however a subject for another post.

Today we’ll talk about why using javascript in SQLcl. Why now isn’t that an old feature? Yes’ it has been there for so long that very few will be using a version of SQLcl that does not support it. Rather than talking about the latest 21c feature that very few would have access to, it may make sense to talk about underutilized features that everyone has access to. In addition, I am late to the party but has recently understood why it is such a great feature.

I remember when it was added and I have seen more than a couple presentations about it. Most of them are either just showing one liners or are showing things like how you could kill a session using javascript. My feeling has always been, cool but I can do that easier and with fewer calls to the database with PL/SQL.

What made me a convert and a lover of this feature is when I had a problem I could not solve with PL/SQL for pure technical reasons. I needed to test round trips to the database. It was a test case where we just wanted to run “select * from dual” over and over, but we wanted each time we did it to be a full round trip to the database. Doing it in PL/SQL does not work as the loop would be in in a single PL/SQL block and it would run all the calls in one trip to the database.

“Does it matter, if you runt close to the database it would be the same speed”. Yes, that was a theory some floated. Especially those who mostly write code that runs outside of the database. The tests showed that it was at least 10x faster to run a tight loop of that SQL in the database.

Why would writing the code in JavaScript change that fact? An autonomous PL/SQL block is sent to the database and then it runs there to completion while JavaScript runs in SQLcl and every executed statement is a full round trip. This is what made the penny drop for me. Learning just a few things about JavaScript in SQLcl gives us a perfect platform to simulate the effect of an applications work against the database.

What about “I already know it’s faster”? Maybe you do, and maybe you are right. But unless you test and show you are no better than the developer who just knows that “the database is slow”. We don’t really know what happens unless we look at data that is measured. Do you know what actually happens in the database without looking at a trace file? No, not really. How do you know how many times the application has to call the database to grab all the data returned by a SQL? How long time does it take between each such call?

Being able to implement that gives you a wonderful simulation tool to show the cost a current solution has and prove that the savings you predict from AWR or trace files can be shown in a simulation.

In addition to that you can also build commands you couldn’t really create otherwise. Like grabbing a second connection and let one inspect what happens when the other runs. Alternatively start many threads performing work in parallell.

Yet another neat feature is to build commands that feels very native to SQLcl. Like how you have a commands that allows you to say “show user” in SQLcl, you could add a “whoami” to display all kinds of information about your session. You could of course do it with a package in the database and go “exec whoami”, but that does not feel as much as a SQLcl command. That is even more true when you want to do something in PL/SQL that you are not allowed to do within the context of a PL/SQL block.