
ThatJeffSmith
1000 FOLLOWERS
ThatJeffSmith blog is about tips and tricks for Oracle SQL Developer, SQLcl, SQL Developer Data Modeler, and Oracle REST Data Services. My primary goal is to help you save time when working with Oracle Database. If I can ALSO make what you’re currently doing a little more fun, then that’s a big bonus for me.
ThatJeffSmith
5d ago
Redwood Theme, Grey
Oracle REST Data Services version 22.4.2 is now available. There are four things of note in this release:
BUG 34943125 : Prehook proc called on PL/SQL Gateway Requests (FIXED)
Data Pump Import Wizard is back, and improved
SQL Developer Web Look & Feel gets a facelift
New Feature: Self Service Schemas! (Docs)
Download ORDS 22.4.2
Upgrading ORDS and Enabling the New Feature
We’re going to use the interactive installer to upgrade ORDS in my database. My pool and configuration files are already in-place.
ords –config c:\ords\23c_config install
Our new feature, ‘Self ..read more
ThatJeffSmith
6d ago
Oracle REST Data Services version 22.4.1 is now available. There are three things of note in this release:
Data Pump Import Wizard is back, and improved
SQL Developer Web Look & Feel gets a facelift
New Feature: Self Service Schemas! (Docs)
Download ORDS 22.4.1
Upgrading ORDS and Enabling the New Feature
We’re going to use the interactive installer to upgrade ORDS in my database. My pool and configuration files are already in-place.
ords –config c:\ords\23c_config install
Now if I want to use this new feature, that we haven’t even talked about yet, I need to enable it at the ORDS configu ..read more
ThatJeffSmith
1w ago
Data Definition Language (DDL) is used to create our database objects. You may need to get this DDL code for an object you’ve lost the ‘source code’ for, or perhaps you want to build an script to create something you’ve been putting together with the GUI.
So we have our table, but how would we get the DDL code?
In today’s post, I’ll demonstrate all the different way to accomplish this task using SQL Developer Web. It’ SQL Developer, but in your browser!
So let’s start with an object, in this case my NETFLIX table.
My NETFLIX table and the DDL behind it.
Let’s use code to generate code!
Use the ..read more
ThatJeffSmith
2w ago
I have kids. Our family has had a family Netflix account since 2017. My daughter decided to jump into 18 Seasons of Grey’s Anatomy last year, and has been binging it ever since.
So when I put together a post last week talking about using SQL to analyze Spotify streaming data, my brain quietly whispered to itself, I bet Netflix data would be more fun.
So let’s do it. Let’s move this data into a database, and use SQL to figure out how much time she’s invested in Dr. Meredith Grey, and her friends.
Step 0: Download your Netflix data.
You request it from your online account. Do that in your browse ..read more
ThatJeffSmith
2w ago
In our previous post, I showed how you could easily download your Spotify personal streaming data, and upload it to a new table in a free (Always!) Oracle Cloud database. We can take our JSON document and with a simple drag and drop, get to a state where we can run SQL!
Reminder –
Read about it instead.OK, I have a table and some data, now what??
Let’s look briefly at our table. I want to see how it’s defined, and what the data looks like now that I have rows and columns.
We can see this if we right-click on our table in the left side of the SQL worksheet and select ‘Open.’
We have four column ..read more
ThatJeffSmith
2w ago
It’s 2023 and you’ve decided you want to beef up your resume with a tech skill that’s proven and lucrative. You’ve decided, NOW is the time to learn SQL. I want to show you how to make this more enjoyable by using your own data.
We’re going to learn some SQL using your Spotify streaming history!
But wait, why not use the time honored classic examples such as EMPLOYEES and DEPARTMENTS?
It’s not JUST because it’s boring, but mostly because it’s very boring.It’s much better to learn with data YOU understand
It’s very easy to write a valid SQL statement and get data back. And you will be tempted t ..read more
ThatJeffSmith
3w ago
Minding your own business as one does, you go to startup ORDS and are suddenly informed that one or more of your connection pools are invalid!
The pool named: |default | is invalid and will be ignored This means there are no database connections for this database being created.
If the pool is invalid, then ORDS can’t connect to the database. And if ORDS can’t connect to the database, then you will see things like your APEX apps not load, your REST APIs not work, or your MODPLSQL apps not come up.
In this post, I want to give you a set of logical steps you can follow to figure out what’s going ..read more
ThatJeffSmith
1M ago
Installed ORDS and set it up for your Oracle Database? Need to quickly test if you’re able to create a REST API for your Oracle schema? Here’s a single PL/SQL call that will in one step:
create a module
define a template
publish a handler
…and all commented!
This will be as short post, you can literally copy and paste this into your IDE, run it on your REST Enabled Schema, and you should have an API to exercise.
When you’re done testing or debugging with this Module, you’ll want to either DELETE or deactivate the service via:
PROCEDURE delete_module
P_STATUS => ‘NOT_PUBLISHED’
The ..read more
ThatJeffSmith
1M ago
Using Oracle SQLcl to maintain our Oracle Database application schemas can be made much easier by using our built-in and extended Liquibase support. Being able to confidently deploy updates to our tables, views, pl/sql programs, etc. via automated CI/CD systems is a next-level, game-changing proposition!
Today I was helping a colleague apply an update to their local database, and it failed!
Honey, I broke the build!
Error report -
ORA-00959: tablespace 'DATA' does not exist
00959. 00000 - "tablespace '%s' does not exist"
*Cause:
*Action:
Rolling back changeset.
The Problem: the changeSet me ..read more
ThatJeffSmith
1M ago
I hope these these types of posts aren’t merely an exercise in ego and humble brag, but then I also like numbers, writing, and keeping track of what I’ve been up to. And maybe you’re curious how others are doing. I know I really appreciate Brent’s candor (and his car collections!) So let’s take a quick look at what happened with the ‘thatjeffsmith’ part of my life and try to do a proper 2022 review.
Career stuff
I’m now managing two product managers on our team, Chris and Zach. I’ve never set out to be a manager or leader, but it happened, and I’m enjoying helping them figure out the whole ‘pr ..read more