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

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.

38 Comments

  1. Hello,
    i have this error when trying to install Ords, i must create admin user or what ? thanks

    Si vous utilisez Oracle Application Express ou si vous migrez à partir de mod_plsql, vous devez entrer 1 [1]:2
    Entrez un nombre pour sélectionner une fonctionnalité à activer :
    [1] SQL Developer Web (active toutes les fonctionnalités)
    [2] SQL compatible REST
    [3] API de base de données
    [4] SQL compatible REST et API de base de données
    [5] Aucune
    Choisir [1]:
    2022-01-17T15:32:46.232Z INFO reloaded pools: []
    Installation de Oracle REST Data Services version 21.4.0.r3481956
    … Fichier journal écrit dans /root/ords_install_core_2022-01-17_163246_00610.log
    … Prérequis de base de données vérifiés
    … Utilisateur proxy Oracle REST Data Services créé
    … Schéma Oracle REST Data Services créé
    2022-01-17T15:32:50.216Z SEVERE Erreur d’exécution du script : ords_grant_privs.sql. Erreur : ORA-01031: privilèges insuffisants
    ORA-06512: à ligne 100

    Pour plus d’informations, consultez le fichier journal /root/ords_install_core_2022-01-17_163246_00610.log

    java.io.IOException: Erreur d’exécution du script : ords_grant_privs.sql. Erreur : ORA-01031: privilèges insuffisants
    ORA-06512: à ligne 100

    Pour plus d’informations, consultez le fichier journal /root/ords_install_core_2022-01-17_163246_00610.log

    • You’re asked for a database account to do the install. You can either use SYS as SYSDBA, or use .sql script as noted in the docs to create a user with the appropriate/required privileges to do the install. What account are you using?

  2. Jeff,

    SQL Developer Web is awesome. Just for clarification, we don’t seem to be able to run the ords.enable_schema as either the SYS or SYSTEM user. Is this expected?

    ERROR at line 1:
    ORA-06598: insufficient INHERIT PRIVILEGES privilege
    ORA-06512: at “ORDS_METADATA.ORDS”, line 1
    ORA-06512: at line 2

    So, for DBA access, you have to enable either the PDB_DBA account or create your own DBA account, right?

    • when i use compte SYS it’s works, but i want to know if i can to create another admin user to execute SQL?
      thanks

  3. Alejandro Peralta Reply

    Hi Jeff!
    In SQL Developer WEB v20.4, is it possible to connect as a proxy user to the database, because it is not allowed to type [] in username?
    Thank’s a lot.

  4. Getting this error when trying to start SQL Developer Web – cannot get to a sign-in page

    An error occurred
    Please make sure you have an stable connection and disable any ad blocker

    Tried few variants …/ords/slq-developer; …/ords/myschema/_sdw; etc

    ORDS installed in Weblogic – connecting via SSL

    Any thoughts?

    • Get it working in standalone before you deploy to WLS.

      /ords/sql-developer is the right URI

      What does the ORDS SO log show when it starts up and when you try to access that URL?

  5. Jeffrey Beckstrom Reply

    I see the instructions for configuring ORDS for SQL Developer but where are the instructions for configuring SQL Developer Web. You have to place it somewhere on the server and tell ORDS where it is don’t you?

    • you enable it for a connection pool…or you don’t – that’s all the config you have

      What are you looking for specifically?

    • Jeffrey Beckstrom

      Don’t you have to install SQL Developer and tell ORDS where it is located or is it part of ords itelf?

    • Hello Jeff,

      I would like to know how we can integrate SQL Developer Web with existing LDAP server?

      Regards,
      Bhaskar

  6. ORS 19.4 was installed successfully.
    select ords.installed_version
    from dual;
    19.4.0.r3521226

    select id, parsing_schema from user_ords_schemas;
    ID PARSING_SCHEMA
    10001 APPS

    :/ords_apptest/ -> is fine APEX developer Console
    :/ords_apptest/sql-developer – > prompt me for username
    EBS apps user and EBS apps password.

    An error occurred
    Please clear your browser’s temporary files and try again.
    :/ords_apptest/apps/_sdw/
    I tried clearing all the temporary files for the chrome browser, this didn’t help.

    • Under /opt/tomcat/log
      localhost_access_log.2020-01-28.txt
      10.10.90.5 – – [28/Jan/2020:10:40:56 -0500] “GET /ords_apptest/apps/_sdw/css/images/sqldev_48x48x32.png HTTP/1.0” 200 4627
      10.10.90.5 – – [28/Jan/2020:10:40:56 -0500] “GET /ords_apptest/apps/_sdw/_services/user/is-authenticated HTTP/1.0” 304 –

    • An error occurred
      Please clear your browser’s temporary files and try again.
      An inspect revealed a 403 error on whoami/

    • HI … I had the same problem.
      Then I found a reference, that SSL is required to run SQL Developer Web.
      So I ssl-enabled my tomcat.
      Now it works fine …

    • SQL Developer Web On ORDS 19.4 Tomcat fails with Error “Clear Your Browser Temporary Cache And Try Again” (Doc ID 2681396.1)

  7. hi Jeff.
    Using sqldeveloper web on the free oracle cloud – how can I change the date format?

    Doesn’t work: alter session set nls_date_format = ‘DD-Mon-YYYY HH24:MI:SS’;

    • Your session only exists for the duration of your query/request.

      So you can’t really.

      What you can do…include that alter session in every query you run (as a script), or build the desired date format into your queries.

      One of the things we need to do going forward is provide application preferences… one of which would very likely include date format.

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

    • Thanks!

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

  9. 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!

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

  11. 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?

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