Gary Myers

Subscribe to Gary Myers feed
I am a proud Oracle developer and this is my blog.
My website is here
This blog is OPINION, SUGGESTION and DEBATE. Please correct anything I write that is misleading.SydOracle
Updated: 2 weeks 2 days ago

Session based sequences in 12c.

Sat, 2013-08-17 21:40
Catching up on some blogs, and I saw this 12c gem by Syed Jaffar Hussain.

Sequences are great. But sometimes they're just a little more unique than you actually need. Sort of like GUIDs. Start churning through them and they're quickly nine or ten digits. There's nothing wrong with long keys from a technical perspective as the extra byte or two of storage is rarely significant. But they can be greedy from a screen layout point of view (especially in tabular forms). And there's a greater chance of getting digits mixed up in email or conversations about them.

If you are doing a nightly or weekly load, it can be nice to load up your half-a-million rows with a 'batch id' and a 'sequence in batch'. Session based sequences are a nice concept that give you scope for keeping those values in a smaller range.

Another trick for reducing the size of IDs is to covert them into HEX. You'll probably only save one character there though, but the mix of alphabetic and numeric characters. TO_CHAR and TO_NUMBER are both happy to use 'XXXXXXXX' format masks to convert between decimal and hex.

WITH enhancements in 12c

Mon, 2013-07-29 15:30
There's been some mentions of this feature on Oracle-base and elsewhere, but here's an example of what excites me.

If you deal with nested arrays/tables in SQL, then you quickly bump into an impedance match. You can't readily get that embedded list into horizontal columns.

I'll use the example I'm most familiar with - 2-D geometry data types.

There's a concept called the MBR (minimum bounding rectangle). You can basically think of it as the most northerly, southerly, easterly and westerly points of an area.

select a.mbr from LGA_2012_AUST a  where lga_name12 = 'Darwin (C)';

SDO_GEOMETRY(2003, 8311, NULL, 
   SDO_ELEM_INFO_ARRAY(1, 1003, 3), 
   SDO_ORDINATE_ARRAY(130.815117, -12.469386, 130.938563, -12.33006))

The MBR is a geometry column with an ordinate array listing the four points.

We can do a Collection Unnesting operation, but then we end up with rows.

select b.*  from LGA_2012_AUST a, table(a.mbr.sdo_ordinates) b 
where lga_name12 = 'Darwin (C)';


To get back to columns, you'd need a PIVOT operation. But because we've lost any sense of the order of those values in the array, that has limitations. In Australia we can easily tell the difference between our latitudes and longitudes, but that isn't possible for many locations.

The WITH operator allows us to extract those array elements easily, and without recourse to a stored function. Though the PRAGMA UDF mention by Tim may mean I don't need to fret about the context switches of switching between SQL and PL/SQL as much.

SQL> l
  1  WITH
  2  FUNCTION ext_val (i_arr in MDSYS.SDO_ORDINATE_ARRAY, i_val in number) RETURN NUMBER IS
  3  BEGIN
  4    return i_arr(i_val);
  5  END;
  6  select ext_val(a.mbr.SDO_ORDINATES,1) v1, ext_val(a.mbr.SDO_ORDINATES,2) v2,
  7         ext_val(a.mbr.SDO_ORDINATES,3) v3, ext_val(a.mbr.SDO_ORDINATES,4) v4
  8  from LGA_2012_AUST a
  9* where lga_name12 = 'Darwin (C)'
SQL> /

        V1         V2         V3         V4
---------- ---------- ---------- ----------
130.815117 -12.469386 130.938563  -12.33006

My demo in SQL*Plus works fine in version 12c.
In the 11.2 Instant Client, I could get it to run using
set sqlterminator #

That stops it treating the ";" in line 4 as a terminator, and allows it to pull in the whole statement. The backslash will send it off to the DB for processing, and it works fine then

SQL Developer 4 also seems to choke on the syntax. I'll have to work up the strength to see if it is already logged as an issue. I assume it won't work in 3.2, and that a 12c rollout will require SQL*Plus and SQL Developer installs to be upgraded. Not sure about TOAD and any other clients.

The Adventures of the Trickster developer - Aliases

Sun, 2013-05-26 21:02
The Trickster is a mythological being who enjoys potentially dangerous counter-intuitive behaviour. You'll often find him deep within the source code of large systems.

The Alias Trap

Generally an alias in a query is there to make it easier to understand, either for the developer or the database. However the Trickster can reuse aliases within a query to make things more confusing.

desc scott.emp
       Name       Null?    Type
       ---------- -------- --------------------
1      EMPNO      NOT NULL NUMBER(4)
2      ENAME               VARCHAR2(10)
3      JOB                 VARCHAR2(9)
4      MGR                 NUMBER(4)
5      HIREDATE            DATE
6      SAL                 NUMBER(7,2)
7      COMM                NUMBER(7,2)
8      DEPTNO              NUMBER(2)

desc scott.salgrade
       Name      Null?    Type
       --------- -------- --------------------
1      GRADE              NUMBER
2      LOSAL              NUMBER
3      HISAL              NUMBER

desc scott.dept
       Name      Null?    Type
       --------- -------- --------------------
2      DNAME              VARCHAR2(14)
3      LOC                VARCHAR2(13)

The trickster can try queries such as

SELECT e.ename, e.grade
FROM scott.emp e 
       JOIN scott.salgrade e ON e.sal BETWEEN e.losal AND e.hisal;


SELECT x.ename, x.dname
from scott.emp x join scott.dept x using (deptno);

As long as any prefixed column names involved are unique to a table, the database can work out what to do. 

If you find this in a 'live' query, it is normally one with at least half a dozen tables where an extra join has been added without noticing that the alias is already in use. And you'll discover it when a column is added to one of those tables causing the database to throw up its hands in surrender. Sometimes you will find it in a dynamically constructed query, when it will fail seemingly at random. 

Once discovered, it isn't a mentally difficult bug to resolve. But first you have to get past the mental roadblock of "But all the columns already have an alias pointing to the table they come from".

SCNs and Timestamps

Thu, 2013-05-16 06:05
The function ORA_ROWSCN returns an SCN from a row (or more commonly the block, unless ROWDEPENDENCIES has been used).

select distinct ora_rowscn from PLAN_TABLE;

But unless you're a database, that SCN doesn't mean much. You can put things in some sort of order, but not much more.

Much better is

select sys.scn_to_timestamp(ora_rowscn) from PLAN_TABLE;

unless it gives you

ORA-08181: specified number is not a valid system change number

which is database-speak for "I can't remember exactly".

That's when you might be able to fall back on this, plugging the SCN in place of the **** :

select * from 
  (select first_time, first_change# curr_change, 
          lag(first_change#) over (order by first_change#) prev_change,
          lead(first_change#) over (order by first_change#) next_change
  FROM v$log_history)
where **** between curr_change and next_change

It won't be exact, and it doesn't stretch back forever. But it is better than nothing.

PS. This isn't a perfect way to find when a row was really inserted/updated. It is probably at the block level, and there's 'stuff' that can happen which doesn't actually change the row but might still reset the SCN. If you're looking for perfection, you at the wrong blog :)

An Oracle April Fools trick

Thu, 2013-03-28 17:00
If anyone is looking for a trick for April Fools' Day, try

alter session set nls_date_format = 'fm';

The result will be an simple TO_CHAR on a date, or implicit conversion of a date to a string, will return NULL. You could try that with an ALTER SYSTEM too.

If no-one notices then, "Congratulations", no-one is relying on the default date format.