SQLcl 24.2 Quick Hit: Generating AWR Reports
ThatJeffSmith
by thatjeffsmith
6d ago
A new feature for SQLcl 24.2 is the ability to interact with Oracle’s Automatic Workload Repository, also known as AWR. This is an Enterprise Edition (EE) licensed feature requiring the Diagnostic Pack. If you have SQLcl 24.2, you can now simply run the AWR command to: list available snapshots create new snapshots create AWR reports based on a start and stop snapshot period of time If you’re curious about what else has been added or fixed in 24.2, be sure to check out Zach’s blog post. AWR Commands awr list snap This will print a list of snapshots in your database. awr create html 358 365 ..read more
Visit website
Using the Keyboard to connect in SQL Developer for VS Code
ThatJeffSmith
by thatjeffsmith
6d ago
I have a file, let’s say, working.sql. I want to do some work on my code until I’m happy with it. And now I want to connect to a database, and execute it…but I don’t want my fingers to leave the keyboard. Of course, you’ll need the SQL Developer extension in addition to VS Code. Demo: Look ma, no mouse! I’m hitting on my Mac, option+command+Z to select a connection, followed by fn+F9 to execute the query. Oh, I also had to use the arrow keys and the Return key to choose the connection I wanted. That looks like this – How to make it work In VS Code, pull up the Keyboard Shortcuts dialog. Then ..read more
Visit website
Oracle SQLcl 24.2, connection attempts and -thick -thin
ThatJeffSmith
by thatjeffsmith
1w ago
The latest version of SQLcl is available today, that version being 24.2. Our SQLcl product manager, Zachary Talke, has an overview blog post here you can read vs scanning the docs. I want to do a deeper dive on these two nuggets – If connection attempts fail there is no longer a retry prompt and you will need to input the startup or connect command again. –thin parameter added to the sql startup command. This sets SQLcl to use the JDBC drivers. Connecting, failing, trying again… 17.2 .. 24.1 I don’t have anything set in terms of Oracle environment variables, and I’m not giving SQLcl really a ..read more
Visit website
Testing a new version of ORDS, without ‘installing’ it
ThatJeffSmith
by thatjeffsmith
1w ago
We put out new versions of ORDS every 3 months, and sometimes more frequently than that. When a new version is available, example 24.2 came out last week, you’ll need to make plans to uptake that sooner vs later. Our new maintenance windows are now 18 months vs 5 years. How to test the ORDS mid-tier components ORDS is composed to two parts: the Java code that runs in WebLogic, Tomcat, or by itself (standalone) the database code that is stored in the ORDS_METADATA schema When you do an ‘ords install’ – you’re either installing or upgrading the database code. When you do an ‘ords serve’ – you ..read more
Visit website
JSON Relational Duality view & API for an ‘EMPLOYEES’ entity
ThatJeffSmith
by thatjeffsmith
3w ago
Remember our friendly Human Resources (HR) schema? And in that schema, remember our EMPLOYEES table? And it has that awesome self-referential key constraint, tying the manager_ID back to an existing employee_ID? No? It looks like this – What if I wanted to work with this ’employee’ as an object or entity in say, my Java or Typescript code? And maybe I’d like to interact with this via an API of the REST persuasion? That’s pretty easy with our new 23ai Database feature, and I’ve talked about that before once or twice. Let’s create a DV for just the EMPLOYEE And the tricksy part, we’re going to ..read more
Visit website
ORDS installs & upgrades, the TNS edition
ThatJeffSmith
by thatjeffsmith
3w ago
I’ve demonstrated both an ORDS install and upgrade scenario here before, but we’ve been making some tweaks lately in order to make things easier, simpler, better. So I thought I’d take a hot minute to show you what that looks like. If you’re looking to see how to fix a bad password with ORDS, this is the post you want. Installation environment I have no existing ORDS configuration. I have a TNSNames.ora file, which ORDS will find via my existing TNS_ADMIN environment variable value. I have Java 17 on my machine. Starting the install I’m going to decide where I want to keep my configuration set ..read more
Visit website
Oracle SQL Developer Extension for VS Code 24.1.1 is here!
ThatJeffSmith
by thatjeffsmith
1M ago
Go get it, now. The changeLog is interesting, even for a ‘dot 1’ release. We have few nice new features for you! Export to Clipboard, vs file On the destination, simply toggle from File to Clipboard, and you’re good to go. Browsing a package spec, but want the body? Just right-click! Dedicated Connections per Worksheet If I open 5 SQL worksheets on a database connection, you’ll end up with 5 separate connections to the database. This means you can have multiple, long running tasks going concurrently. It also means you can be much more productive, or consume many more resources than you could ..read more
Visit website
Searching JSON Arrays with JSON_EXISTS and REST APIs
ThatJeffSmith
by thatjeffsmith
1M ago
This is a follow-up to this post, “Retrieve DISTINCT list of values from JSON arrays via SQL” – while it’s not mandatory you read that first, I highly recommend it. TL;DR summary I have a table called, ‘MOVIE,’ and in that table are one or more JSON columns. The ‘raw’ data looks like this – I want a REST API to search my movies, by GENRE And actually, I want to be able to pass 2 GENRES, and find those that match, BOTH. So for example, what movies are BOTH a ‘Horror,’ AND a ‘Comedy.’ The SQL to do this isn’t very hard. select * from movie where json_exists(genres, '$[*]?(@ == "Horror ..read more
Visit website
Oracle REST Data Services Installer Improvements for 24.1
ThatJeffSmith
by thatjeffsmith
1M ago
The Interactive Installer is now a bit less, interactive. Download ORDS 24.1.1 And by that, I mean there should be FEWER prompts, less back and forth. Which means you get to your SQL Developer Web/APEX web pages faster, or your REST APIs being served sooner. ./ords --config /opt/ords/conf23ai install Change #1: we’ll find/use your TNSNames.ora files I happen to have one, and yes, I would like to use it. So instead of typing in, successively, ‘localhost,’ ‘1521,’ and ‘FREEPDB1’ – I can simply say, hey go use Service #2. And now I can proceed to providing the administrator credentials for my d ..read more
Visit website
Retrieve DISTINCT list of values from JSON arrays via SQL
ThatJeffSmith
by thatjeffsmith
1M ago
I have a MOVIE table. My data looks, something like this a relational table with various text, numeric, temporal types several JSON objects or arrays of objects stored as JSON And here’s exactly what it looks like: REST API: GET the distinct list of GENRES If each row had a single value, acquiring a distinct list of those values would be very straightforward, e.g. the list of years the library encompasses: select distinct YEAR from movie order by 1 asc; But alas, I don’t have a single value per row. I have these JSON objects or array of objects. I need a distinct list of say, GENREs or A ..read more
Visit website

Follow ThatJeffSmith on FeedSpot

Continue with Google
Continue with Apple
OR