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.’

The navigator lets you pick your schema, type of object, and has a search to filter down the list of objects.

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

Bam! Note we add the ‘create or replace’ – the rest of the code comes straight from the DB.

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

Default editor is the SQL worksheet. But I can ask for a new PL/SQL editor

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

The blank screen of panic! Fewer buttons on the toolbar for starters.

So what do we have here?

The Toolbar

Similar to what you see in the SQL Worksheet, but fewer buttons.

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:

Boo! I know I AT least forgot a semi-colon on line 9.

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.

I know my example is dumb, just bear with me.

Alright, so let’s fix that.

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

The script output window keeps a running log of the compiler feedback.

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:

Type ‘DBMS_CLOUD’ and hit enter.
We found it!

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

Almost there!

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!

There’s our program!

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!

This is nice for quick pan-and-scans of your code.

Don’t like the minimap? Turn it off!

Click by your USER name in the upper right hand corner of the app.

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

There are many more look and feel items on this page, so have a look around sometime.

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

Up pops the search panel, type in your string, and instant navigation to the first occurrence.

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

There’s an entire command palette!

Right click or Ctrl+Shift+H

There are many, many powerful editor features here. Search for it by typing the name.

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

This was recorded in the SQL Worksheet, but works in ANY editor for SQL Developer Web.

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!

A lot of power at your fingertips, literally.

Don’t forget the Insight/Code Completion feature.

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

Got my column list for the cursor!

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!

No keyboard shortcuts assigned to these items, sorry.

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.

You have give our app permission first, but only once.

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.

There’s my DBMS_OUTPUT, we grab that for you, automatically.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

8 Comments

  1. 22.4 is close to perfect! )))
    But would You clarify how i can get Your black theme and chenge date format columns?
    Appreciate!

    • High Contrast Dark setting in theme – first box on the code editor preferences.

      How do you mean, change date columns?

    • Appreciate for answer.
      I mean a date format of table’s columns or SQL result sets having a date type. I’d prefer locale with dd.mm.yyyy hh24:ss

    • We convert all dates to timestamps with timezones and return as UTC. You can change that to local time zone in preferences for ‘Region.’

      You’ll need to use a to_char() function if you want a specific date format.

    • Look, Oracle DB host has local time UTC+5

      Query SELECT SYSTIMESTAMP FROM DUAL returns:

      SQLC
      28-DEC-22 10.25.34.688839000 AM +05:00

      SDW preference Time zone=LocalTime Zone (by the way it determines correct UTC+5)
      2022-12-28T05:27:40.057Z

      SDW preference Time zone= UTC
      2022-12-28T05:27:03.051Z

      In the same time running
      select to_char(SYSTIMESTAMP,’DD.MM.YYYY HH24:MI:SS’) from dual
      in that three cases returns one correct time 28.12.2022 10:27:03

      From my modest viewpoint Time zone=LocalTime Zone looks incorrect…

  2. Rajeshwaran, Jeyabal Reply

    one quick question in comparison with SQL Developer Desktop version (Vs) Web version

    when i do “…show parameter cursor_sharing…” from Desktop version – it get this as output


    NAME TYPE VALUE
    -------------- ------ -----
    cursor_sharing string EXACT

    where as in the web version of SQL Developer (aka Database actions) – got this error


    Restricte at SP2-0738: command: "SHOW PARAMETER" not available

    • Yes, some commands are restricted. One of those is ‘SHOW’ – because it can expose client side information, which in this case would be the webserver that ORDS is running on – not your machine where Chrome is running.

Write A Comment