Working with PL/SQL in Oracle SQL Developer Web

PL/SQL in Oracle SQL Developer Web now rates it’s own code editor, vs doing everything in the base SQL Worksheet.

Download Oracle REST Data Services – run SQLDev Web on your own development rig!

Do you have some PL/SQL programs you need to update, browser, or even write from scratch? You could be doing this work in your favorite web browser!

With Oracle SQL Developer Web 22.1 and higher, we optimized the development experience for PL/SQL (vs just always assuming, write SQL-run SQL.)

What we added

• open / save files directly from your browser and to your local machine
• a dedicated editor for PL/SQL

Let’s take a look at the editor!

You can start from the SQL Worksheet.

Option 1, browse to the existing PL/SQL program, right-click, and ‘Open.’

Once I click ‘Open,’ I’ll be put into my new PL/SQL editor.

Your other route it take a fresh stab at the program. In the SQL worksheet, click the dropdown control on the ‘New’ editor button.

Clicking that brings me to a slimmed-down editor, more tailored for PL/SQL code.

So what do we have here?

The Toolbar

You can:

• re-open previous editors you may have saved
• ask for yet another new SQL or PL/SQL editor
• OPEN and SAVE files (those are new)
• COMPILE
• Format your code
• Simple FORMAT (just change case of keywords and identifiers.)

After our toolbar, we have the actual editor area space itself. We’ll need some code to work with.

Let’s write some code and do a compile.

create or replace procedure this_is_not_very_good (x in integer) IS
  y varchar2(10);
  z date;
begin
    y := x || '12-1999';
    z := to_date(y, 'MM-DD-YYYY');
    dbms_output.put_line
      ('This is dumb, but here is a day from December of 1999:' || z)
end;

And then we’re going to hit the ‘Compile’ button, and:

The compiler is telling us it got to line 9 of our program and the word it ran into, ‘end’ – made no sense based on what it saw on line 8.

Let’s pretend this was a 2500 line program, what’s the fastest way to get to the problem area?

Click the error to navigate.

Alright, so let’s fix that.

Add the semi-colon, and hit the compile button again.

Let’s look at a bit bigger program…say, DBMS_CLOUD?

Now, where is this package in Autonomous? I went looking for it in the SYS schema but came up empty. And then I remembered I could use our new SEARCH feature.

Click in here:

Oh, it’s a common object via C##CLOUD$SERVICE. I can hit Ctrl+1 to navigate to it in the worksheet browser.

Right click on the program, and ‘Open.’ Since it’s a package, I need to pick either the Spec or Body…and blammo – I have my program open to investigate!

I enjoy going through the Package SPEC to read the comments. Those are written by the developers, so it’s like the doc but straight from the engineer’s mouth, so to speak.

There’s a few tricks you can use to navigate.

The Minimap

Look to the far right gutter, there’s a picture of the code. Now, comments in my editor show up as GREEN. So I could use the scrollbar to quickly go down-down-down till I find a big block of green code and stop.

Found it!

Don’t like the minimap? Turn it off!

Once you open the preferences, navigate to the ‘Code editor’ section and disable it.

We were looking for stuff, right? How about a search?

There’s no ‘Find’ button on the toolbar, darn it.

But that’s OK, it’s much easier than you imagined. JUST –

Search with Ctrl/Cmd+F

There’s a lot more than Ctrl+F available.

There’s an entire command palette!

Right click or Ctrl+Shift+H

Want a live demo? Well, how about a pre-produced animated GIF?

I’m looking to add a widget to the app that would easily show you ALL the keyboard shortcuts available, so stay tuned. They ARE in the Doc – so search the doc!

Don’t forget the Insight/Code Completion feature.

Writing SQL in your PL/SQL? You can get help, just like in the SQL worksheet.

Yikes your code is really big!

Not the amount of code, but he literal size of it.

Someone asked if there was a ‘Zoom’ feature. Yes, via the command palette!

What about opening/saving files and executing our PL/SQL?

Yes, you can absolutely do that, depending on your browser. Details in the blog post link below.

I’ve talked about opening and saving files in more detail here.

OK, I have the program open, done, and ready to run. Now what?

For executing the program, that’s not integrated into the PL/SQL Editor…yet. If you right-click on the program and say ‘Run.’

That will pop-up a dialog to set your parameters, which will generate a PL/SQL block of code, that you can put into the SQL Worksheet.

I want this to be integrated INTO the PL/SQL editor so you don’t have to toggle back and forth stay tuned on that as well.