Monday, March 24, 2008

oracle cast function in stored procedure

There is a thing can be called stupid in oracle. I can't understand the reason!
This is a piece of code in a Oracle Trigger. Decimal numbers are compared roundly by double precision.

if (cast(to_char(:new.unit_price) as number(14,2)) = cast(to_char(v_ppl_unit_price) as number(14,2))) then
:new.unit_price := v_temp_new_unit_price;
end if;

But after executed this code, an error is occurred:

Error: PLS-00103: Encountered the symbol "(" when expecting one of the following:
. ) @ %
The symbol ")" was substituted for "(" to continue.
Line: 122
Text: if ( cast(:new.unit_price as number(14, 5)) =

And i found out how to fix it.
that is declare a subtype, and use it on cast:

declare
subtype number_14_2 is number(14, 2);
...
begin
...
if (cast(to_char(:new.unit_price) as number_14_2) = cast(to_char(v_ppl_unit_price) as number_14_2)) then
:new.unit_price := v_temp_new_unit_price;
end if;
...
end

It's done! How strange!

No comments: