Learning is not a spectator sport

Complete listing of posts

  • Dealing with URL’s

    If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier SQL> create or replace function utl_url_escape(x varchar2) return varchar2 is 2 begin 3 return utl_url.escape(x,true); 4 end; 5 / Function Read more

  • Opatch quick tip

    I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error: C:\oracle\stage\22581007>opatch apply Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : C:\oracle\product\1210~1.2 Central Inventory : C:\Program Files\Oracle\Inventory from : n/a OPatch version : 12.1.0.1.10 OUI version : 12.1.0.2.0 Log file Read more

  • Median in SQL

    Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial query can determine the median salary from the EMP table SQL> select avg(sal) from 2 ( select x.sal 3 from scott.EMP x, scott.EMP y 4 group by x.sal 5 having sum(sign(1-sign(y.sal-x.sal))) in 6 Read more

  • Are your SQL Plus scripts going to ‘ell ?

    Excuse the hideous pun in the title.  All will become clear shortly. Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database. Here’s a simple script I want to run: and here’s my very sophisticated batch file (which could just Read more

  • Up to date text indexes

    If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example, SQL> create table t1 ( 2 x int, 3 y varchar2(1000) ) 4 ; Table created. SQL> SQL> create index t1_iz Read more

  • India bound !

    As part of the activities organised by the Oracle Ace Program and the All India Oracle User Group, the OTN Yathra tour is coming up in a few weeks. I’ll be speaking on 12c developer features and advanced SQL techniques at the following locations: 23-Apr-16 Saturday,  Chennai 24-Apr-16 Sunday, Bangalore 26-Apr-16 Tuesday, Hyderabad 28-Apr-16 Thursday, Read more

  • The death of the demo script

    On AskTom, one of the things we always strive to do is provide demo scripts in our answers where appropriate.  That way, people can see our line of thought, and hopefully get an understanding as to how we arrived at a solution.  And there’s two very important concepts in that last sentence: our line of Read more

  • Views as “stored text”

    You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines. This is by and large true, but don’t forget, this is not  the same as saying we simply take whatever text you give us, and store it. Let’s take a look at Read more

  • Database dictionary corruption ? Maybe not.

    At first glance, this looks like a major drama. Some sort of database dictionary corruption, or internal error. But we’ll see, it’s actually fine. Let’s do a simple describe on an existing table SQL> desc MY_TABLE Name Null? Type —————————– ——– ——————– EMPNO NUMBER(38) ENAME VARCHAR2(30) HIREDATE DATE DEPTNO NUMBER(38) SAL NUMBER(38) Now of course, Read more

  • The first matching row

    I was recently asked when presented with a query along the lines: SQL> select * from 2 ( select x 3 from t1 4 where x = :b1 5 union all 6 select x 7 from t2 8 where x = :b1 9 ) 10 where rownum = 1 11 / and asked – will Read more

  • Tightening up your data model

    Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains. Here’s a little routine that lists those columns that probably need a NOT NULL constraint. It looks at all columns and check to see if a constraint with definition “COLUMN_NAME” IS NOT NULL Read more

  • TOP-N histograms on 12c

    I had an interesting question on AskTom today, with a nicely provided test case !!!,  so I thought I’d share it here with some additional commentary. The basic premise of the TOP-N histogram in 12c is that when the number of distinct values in a column exceeded the number of available buckets for histograms, the Read more