Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: sql
External table preprocessor on Windows
There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing: run_os.bat ========== @echo off cd \oracle dir /b SQL> create table…
Less slamming V$SQL
It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are…
MERGE and ORA-30926
Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but…
The death of UTL_FILE – part 2
I wrote a post a while back call “The Death of UTL_FILE”, and probably because of it’s click-bait title I got lots of feedback, so I’m back to flog that horse . Seriously though, I stand behind my assertion in that post, that the…
LISTAGG hits prime time
It’s a simple requirement. We want to transform this: SQL> select deptno, ename 2 from emp 3 order by 1,2; DEPTNO ENAME ———- ———- 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 BLAKE…
Another day…another "use the right datatype" post
Here’s an interesting little oddity (aka bug) with scalar queries. We’ll start with a simple working example SQL> create table t1 ( c1 number ); Table created. SQL> insert into t1 values (1); 1 row created. SQL> create table t2 ( c1 int, c2…
18c and the ignoring of hints
One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the…
