-
Extended VARCHAR2 and the hidden LOB
In 12c, there was much rejoicing when VARCHAR2 columns could be extended from beyond their current 4000 byte limit to 32767. But whatever the limit is, an Oracle block is an Oracle block and there are limits to what you can fit inside it. Hence an extended VARCHAR2 column is stored as a CLOB under… Read more
-
ALL_ARGUMENTS missing information?
I made a interesting discovery earlier this week when I was doing some analysis for some AskTom database procedures when prepping some regression tests. Here is a quick summary so that you won’t get the surprise I got. In 11g, if you create a packaged type and procedure that references that type, for example as… Read more
-
Free space requirements for ALTER TABLE
One of the very cool features from 12.2 onwards is that the vast majority of segment operations that require rewrite of the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when… Read more
-
The podcast is go!
A few weeks back, I posted that I would give podcasting a trial. I’m thrilled to see that the response has been very supportive, with plenty of downloads and listens, so (as you hear at any keynote launch nowadays). “Today, I’m proud to announce…” …that the podcast is now available on the popular podcast platforms.… Read more
-
Expanded TYPE definitions for PL/SQL 21c
There has always been that odd conflict between the language you use for SQL versus the language you use for PL/SQL when it comes to expressing what appears to the be the same thing. PL/SQL: Do want a datatype that contains two elements? Use a RECORD SQL> declare 2 type MY_EMPS is record ( 3… Read more
-
Counting business days between 2 dates
One of the most commonly hit questions on AskTom is how to count the number of work days (Mon => Fri) between a start and end date range. This is not a particular tough problem to solve since we have easy access to the day of the week via TO_CHAR, thus simply cycling between the… Read more
-
Blockchain tables are here in 19c!
Slow down there….Take a breath. Because if you’ve read the title of this post and you’re already logging on to your 19c database, I need to you STOP. Before you go anywhere, please read this post first. OK … lets proceed, now that you’re not going to end up with a table you can’t drop!… Read more
-
Blockchain tables in 21c? A word of warning
As I write this, the moment you do a browser search for “blockchain”, the last thing you will get a match on is the technology elements. Instead, my results are flooded with hedge funds, short selling, Gamestop and bitcoin. A friend has been sending me messages along the lines of “Is it true Satoshi Nakamoto… Read more
-
Datatype conversions – strange internal function
Perhaps the most famous (or infamous) performance tuning problem you’ll find spanning decades of blog posts when it comes to Oracle and SQL is the “mystery” of why Oracle is not using an index when it is totally obvious to us as the developer that it should be. The demo code is always along the… Read more
-
New Year … new idea … new channel
I’ve done a lot of presentations over the years, written a ton of blog posts, and over the past 5 years cranked out hundreds of tech videos of my YouTube channel. But with the current state of the world, I also know that over the last year we’ve been awash with tech content that now… Read more
-
PL/SQL in 21c gets amazing new loop iterators
“It’s a dead language” people said. “There’s never anything new” people said. Well, let me serve those people a slice of humble pie as we take a look at huge suite of enhancements to iteration handling in PL/SQL in 21c. In particular, there is one thing that I really find nice about these new changes.… Read more
-
How long will the script take to run?
In a world where databases are now the norm, whereas distributing data via a CSV file used to be commonplace, nowadays we often see the ubiquitous INSERT script being offered as a means to seed data. This is perfectly fine for those requirements where we are populating a finite list of reference data that is… Read more
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
Learning is not a spectator sport 