FOR EVERYONE!

Oracle SQL Developer Web
See that URL – I have ORDS running on my local machine, this isn’t the Oracle Cloud (not that there’s anything wrong with that.)

Well, for everyone with access to Oracle REST Data Services version 19.4.

Wait, what is SQL Developer Web?

We built it FOR the Cloud, the Oracle Cloud. It was in available in our first DBaaS offering in OCI-Classic, and now it’s available in the Oracle Autonomous Database. We have plans to make it available throughout ALL of OCI, regardless of where or how your Oracle Database is running, but for now, you can also stage your own copy of ORDS and host SQL Developer Web wherever you’d like.

Running in the browser, you get screens for:

  • running queries and scripts (Worksheet)
  • wizards for creating and editing schema objects
  • creating database diagrams (Modeler)
  • Performance Hub (Real Time SQL Monitoring and ASH)
  • Importing data (to new and existing tables) from CSV and Excel
  • Reports for sessions, alerts, logins, parameters, and Top SQL
  • an ADMIN page for creating, managing, and REST Enabling database users

Here’s a quick 3-video run down of SQL Developer Web as it’s running today in the Oracle Cloud.

Setting up ORDS for SQL Developer Web

So you want it now? And how do you go about setting it up?

  1. Install ORDS
  2. Configure ORDS for SQL Developer Web
  3. REST Enable a database user
  4. Login!

The ORDS version 19.4 Docs detail how to do an ORDS install, but in the meantime, if you use the ORDS installer, there’s a very simple question you get to answer:

The default is to ENABLE SQL Developer Web.

1 – Enable SQL Developer Web (which will also enable REST Enabled SQL)
2 – Enable ONLY REST Enabled SQL
3 – Do not enable either SQL Developer Web or REST Enabled SQL

Once the installer has finished, if you peek into the defaults.xml in your configdir, you’ll observe:

<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>

BOTH of these must be set to TRUE. REST Enable SQL is what provides the ability to run ad hoc SQL and PL/SQL on your database. You can see this when you run something in the Worksheet:

Logging In

The URL: http://localhost:8080/ords/sql-developer — don’t click this, and this is only if you’re running standalone on the default port.

Type in your REST Enabled SCHEMA name

Wait. How do I REST Enable a Schema?

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/

After I run this, the HR user can publish RESTful Services, REST Enable objects in that schema, AND login to SQL Developer Web.

Take a tour!

The first time you login, we’ll present a tour for the Worksheet and Modeler.

Click the Binoculars button to re-open the tour after your first visit.

Need even more help? Click the ? (Help!) button.

Our Docs team Rocks!

Access to Features

Everyone who logs in should see the Worksheet and Modeler pages. However, if you login as a user who has the DBA (and PDB_DBA) roles, then you get all that extra stuff I mentioned previously.

Like the PERF HUB.

Assuming your user has access to the required Views and PL/SQL APIs, naturally.

One last thing, Loading Data

You have two options. You can load data to an existing table (right-click on the table in the worksheet browser), or you can drag and drop your Excel or CSV file directly into the import area in your worksheet.

Once I do that, I get a wizard…

Preview your table and column definition, then we’ll create and load the table.

Other 19.4 things of note

There was no version 19.3. I mean, there was, but it only shipped to Oracle Cloud. And by the time we got it ready for on-premise, it was time to put out 19.4.

ORDS is faster (see the release notes).

SQL Developer (desktop) has a ton of bug fixes from the community forums – thanks everyone! The biggest one is the issue with large (32k+ exports not working).

thatjeffsmith
Author

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

15 Comments

  1. Mikhail Velikikh Reply

    I don’t see a dedicated post to SQL Developer 19.4, so I just add a comment to this post.

    The OTN downloads page (https://otn.oracle.com/ which redirects to https://www.oracle.com/technical-resources/) still point to SQL Developer 19.2 (it’s in the Downloads tile):
    https://www.oracle.com/tools/downloads/sqldev-v192-downloads.html
    Notice please that it is a 19.2 version. Could we have it updated to the most recent version?

    If I expand All Downloads -> Developer Tools, the SQL Developer link points to the correct location:
    https://www.oracle.com/tools/downloads/sqldev-downloads.html

    • thatjeffsmith

      Thanks!

      I just submitted a request to our web team and hopefully should be fixed by this time on Wednesday.

  2. After realizing that recipe just got an error (in browser after entering user name and password of ords-ed scheme just ):
    An error occurred
    Please clear your browser’s temporary files and try again.

    Would You advice where to dig further?

    • Well, i’ve got a result without any problem on production DB with https access and true domain URL. It seems my question concerned some local network http URLs issues.
      Anyway thank You for Your help!
      It looks great and i guess web-sql-developer will get more functionality further!

  3. Piotr Rzepecki Reply

    Sorry, I was not clear and I was referring to Oracle SQL Developer (desktop) since that version is also mentioned in the article.

    • Piotr Rzepecki

      Since I am also using Kerberos authentication then I am using slightly adjusted method one where:
      – username: []
      – password:
      and with Oracle Instant Client and “Use OCI/Thick driver” option selected it works like a charm.

    • Piotr Rzepecki

      Since I am also using Kerberos authentication then I am using slightly adjusted method one where:
      – username: [some user]
      – password: blank
      and with Oracle Instant Client and “Use OCI/Thick driver” option selected it works like a charm.

  4. houshmand rastin Reply

    Unable to retrieve available ORDS schemas. upgrade APEX from 19.1 to 19.2 and ords to 19.4! anyone has similar issue?

  5. Nice work :).
    Is it possible to connect as a proxy user to the database, because it is not allowed to type [] in username?

    Thanks and regards

    • Piotr Rzepecki

      Well, connecting as proxy user works but it needs Oracle Instant Client and “Use Oracle Client” with “Use OCI/Thick driver” options selected.

Write A Comment