FOR EVERYONE!
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?
- Install ORDS
- Configure ORDS for SQL Developer Web
- REST Enable a database user
- 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:
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.
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.
Need even more help? Click the ? (Help!) button.
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.
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…
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).
38 Comments
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?
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?
Correct, no can do for SYS. And that SHOULD be documented, see the PKG spec…
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
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.
No, that’s not supported, yet.
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?
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?
Don’t you have to install SQL Developer and tell ORDS where it is located or is it part of ords itelf?
SQL Developer Web is an ORDS feature – so it’s just part of the ORDS package.
Hello Jeff,
I would like to know how we can integrate SQL Developer Web with existing LDAP server?
Regards,
Bhaskar
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/
Please open a Service Request with My Oracle Support so we can properly help you.
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 …
Ssl not required but absolutely recommended!!
SQL Developer Web On ORDS 19.4 Tomcat fails with Error “Clear Your Browser Temporary Cache And Try Again” (Doc ID 2681396.1)
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.
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.
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?
What’s the URL you used to login, and what schema did you rest enable?
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!
Sorry, I was not clear and I was referring to Oracle SQL Developer (desktop) since that version is also mentioned in the article.
See this
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.
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.
Unable to retrieve available ORDS schemas. upgrade APEX from 19.1 to 19.2 and ords to 19.4! anyone has similar issue?
Unable, how?
Upgrading APEX is completely separate from upgrading ORDS.
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
No, that’s not supported.
Well, connecting as proxy user works but it needs Oracle Instant Client and “Use Oracle Client” with “Use OCI/Thick driver” options selected.
I thought we were talking about SQL Developer Web.