Posts Tagged ‘sql’
View Source of Oracle Trigger
Wednesday, May 11th, 2011
select trigger_body from user_triggers where trigger_name = 'XXXXX'
Tags: oracle, sql
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: oracle, sql
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: sql
Posted in Development | No Comments »