Posts Tagged ‘oracle’

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 »

Using Ibatis Typehandlers to fix Oracle Date “missing time”

Friday, June 25th, 2010

The Problem

When using Ibatis to retrieve Date type values, on Oracle 10g (some driver versions), the time portion of the value returned from the database is not mapped onto the Java object.

i.e.

In DB: 02-JAN-10 13:30:00
In Java Object after mapping: 02-JAN-10 00:00:00

The Ibatis ResultMapper, snippet, looks like this:

....
<result property="savedDate" column="SAVED_DATE" javaType="java.util.Date" jdbcType="DATE"/>
....

(more...)

Tags: , ,
Posted in Examples | 2 Comments »

How to convert a CLOB to an XMLType in Oracle

Thursday, June 3rd, 2010

Not much comment to add here; code says it all

PROCEDURE clobToXMLType(myClob IN CLOB)

IS
    l_xmlType XMLTYPE;
    -- do something
BEGIN
    l_xmltype := XMLTYPE.createXML(myClob);

EXCEPTION

    WHEN OTHERS THEN
    	RAISE;

END clobToXMLType;

Tags: , ,
Posted in Development, quick tips | No Comments »