If you subtract one timestamp from another in Oracle, the result is an interval type. This is similar to how subtracting a DateTime from another in .NET yields a TimeSpan. Unfortunately, unlike .NET, Oracle provides no simple equivalent to TimeSpan.TotalSeconds.
Here's the SQL to accomplish it. Note that fractions of a second are included in the result.
declare
diff interval day to second :=
cast('18-DEC-08 11.00.00 PM' as timestamp) - cast('18-DEC-08 9.59.58.5 PM' as timestamp);
secs number;
begin
select
extract(day from diff) * 86400
+ extract(hour from diff) * 3600
+ extract(minute from diff) * 60
+ extract(second from diff)
into
secs
from dual;
dbms_output.put_line(secs);
end;
