Posts Tagged ‘sql’

View Source of Oracle Trigger

Wednesday, May 11th, 2011

select trigger_body from user_triggers where trigger_name = 'XXXXX'

Tags: ,
Posted in quick tips | No Comments »

Using Oracle’s SYS_GUID()

Friday, April 29th, 2011

Background

For years I’ve bemoaned Oracle’s lack of an auto-number/identity type column (think MySQL AUTO_INCREMENT). Obviously you can create sequences and triggers to produce the same effect but it always seemed like a bit of a palaver to me.

So, I had a smile on my when I discovered the delight that is SYS_GUID().

Taken from the Oracle documentation:

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

Example

select SYS_GUID() from dual

  SYS_GUID()
= ================================
1 A20D42BA1F165DB5E04400144FB99F0A

SYS_GUID() as a default

Because SYS_GUID() “returns a globally unique identifier” we can use it as a default value in a Primary Key column of a table.

create table products( 
    product_id raw(32) default sys_guid() not null primary key
    ............
);

Tags: ,
Posted in Development | No Comments »

Group by SQL

Saturday, February 26th, 2011

For some reason I can never remember this simple piece of “group by SQL”. Some sort of mental block

SELECT foo, COUNT(foo) AS theCount FROM bar GROUP BY foo 

Tags:
Posted in Development | No Comments »