I recently put together a 30 minute ‘power session’ for our Oracle Database World event. If you missed it, you still have one opportunity to attend the simu-live event on December 1-2. We’ll be there live to answer your questions and take you on a guided tour of our hands-on LiveLabs.
If you’d like to see my Tips & Tricks session, you can watch that now.
This Tips & Tricks session doesn’t just cover SQL Developer and SQLcl. It ALSO covers SQL Developer Web, ORDS, and SQL Developer Data Modeler.
AND, it includes a bonus tip from Maria Colgan!
Q&A from the Americas and European Events
Is there a prebuilt CLI available for calling the OCI ORDS Server for doing this i.e. creating the Oauth clients?
You can invoke the OAUTH PL/SQL API from SQLcl…or use the OAUTH2 GUI in SQL Developer Web to manage your Clients.
How would I find the URL for the SQL Developer Web if the admin would not give us any information (all we have is the wallet to connect to the ATP) and we use that with our IDE to connect to the Cloud
Ask your admin, very nicely to share it. Otherwise it sounds like you’re asking how to hack into your system, which I obviously can’t help with.
If we use ORDS With OAuth for the web tier, is the DB connection via a shared user/schema in which case how can we identify the end user inside the database?
You can query :current_user within the handler code block which will give you the client ID – I talk about that here.
The developers are not given access to OCI console (Database tools). The only we can access the ATP is from our IDE. How do we REST enable?
REST Enabling is available in SQL Developer desktop via a simple right-click, or you can invoke the ORDS PL/SQL APIs manually in a script or EXEC command.
Can you use custom domain with oracle ADB rest api?
Yes, absolutely.
We use ATP, and if I am going to try out REST on packages & procedures, am I going to start any new services and affect any performance (and upset DBAs)?
Only if your services are poorly coded in terms of the SQL and PL/SQL you use to build those services.
There are a lot of DB side features for data redaction/restriction meaning BOB and ALICE might see different results on the same query. Are an ORDS/REST applications able to still use these?
Yes! The REST APIs run under a schema, any security rules written for BOB or ALICE will apply the same regardless if invoked via HTTPS or via SQLcl/SQLDev.
Do you need any special privilege to REST enable?
You need to own an object, or you need the ORDS_ADMIN role in the database.
what if the PLSQL procedure has 50 parameters? Would you build a big query string? And what about if there is types as parameter (structure)?
Types are supported and you’d have a JSON payload on the POST request. If you want to pass parameters on the query string, you could have 50, but it would be ugly.
Does REST enabling your app meaning moving security outside of database users/grants and if so wouldn’t that make it incompatible with something like DB Vault?
There are two layers of security, what the mid/web tiers let pass through to the database, and then what the database allows once you’re inside the database.
DB Vault as in the sense of building security realms and command rules etc. Ultimately meaning that BOB and ALICE might see different results to the same query – would it be compatible with a REST/ORDs application? Because on the DB side the REST/ORDs application might always connect as the same database user?
You can use our prehook feature to run custom pl/sql at the authentication/authorization step to set database session parameters, etc. In general database users are NOT web/actual users though.
Are there any example systemd unit file examples for running ORDS from linux?
We have RPMs for ORDS on Linux avail via YUM.
Are the “info+” elements a report of what’s really in the data? Or are they reports on what’s in the stats?
It’s reporting what’s in the database stats – we don’t query the data itself, that would be terribly expensive.
Excellent presentation, we use all of those tools, and we could never done it without all the demos
Ah, thanks that was very nice of you to say.
Is there a way to prevent ORDS autonomous from showing adb url ??( in case of use vanity url), was not able to set HTTP_ERROR_STATUS_ON_ERROR_PAGE_ENABLED
No, we manage the mid-tiers for you in Autonomous.
What version of SQL Developer is being presented?
21.2 for desktop and 21.3 for Web.
IS there a docker image available for the latest version of Oracle XE?
Which SQLDev version is recommended for most of the features shown ?
Always, the latest (21.2)
Does ORDS require weblogic or can you run ORDS in production as a FatJar?
We don’s require WLS, but if you want to use it, it’ll be just fine there. if ‘FatJar’ means run as a standalone java application, absolutely we support that for production.
Can I create a REST service on an Autonomous Database?
Yes, yes, and more yes.
Jeff the current report you are showing is present in sql dev 21.2.1 ?
They were talking about a Web Dashboard in SQL Dev Web. We do have reports and charts in SQL Dev desktop, but not quite the same as what we have in the Web.
Can you run Oracle Database Actions page on premise with an oracle 19c environment?
YES.
Can SQLcl search and edit history with VI/vim commands?
No, but I want that feature myself, so stay tuned.
How to link SQLcl with VI or Emacs?
You can control this via the ‘set editor’ command, but latest SQLcl runs with it’s own editor/vi built-in by default, or you can switch to Emacs if you’re a nerd.
Did we update the OCI Test option to work with the 19c client? — It stopped working recently
Make sure you’re on 21.2.1 – that’s been fixed to be A-OK with 21c or 19c clients.
when we cast a column in SQL to a type (with multiple columns and rows), we are not able to see the actual data in SQL Developer.
SQL Developer is sometimes heavy and irresponsive. How to solve this
I wrote an entire post about this.
Does oracle XE run on arm? This is so that I can run it on my apple silicon based linux VM.
Not yet, but we’re working on it – for the database in general, not just for XE.
Can we import notepad++ file 2gb into oracle table?
What Java Version do you need in your mac to run SQL Developer? Does SQL Developer run on apple silicon?
I’m hearing 11 or 17 will work.
Can you use PLSQL for micro services?
Absolutely you can, and ORDS is perfect for making those PL/SQL programs available from your applications via HTTPS.
