Saturday, March 31, 2007

Never use Long datatype in Oracle

I had a problem...............
I was using Varchar2 for a column.....
The datasize for that column increased.................
I searched which datatype to choose from which could hold alphanumerics and also larger in size than varchar2.....
I found and used Long................

Now at some other time i had to enable auditing on this table through DML trigger.....
It was not allowing to compile it correctly.

I came to know many shortcomings of Long.

"Long columns can not be accesses using :new or :old"

I cursed the day i used this datatype rather than LOBs.

There is only one way that you can use as a work around.

Dont use long attribute of a tuple using :new or :old but through primary key get the value of Long column for this particular tuple (using Select Into)
Now you can use this value for auditing.
There are mutating table problems still..........which you can handle.