Re: sysdate while updating

On Thu, 07 May 1998 14:39:17 -0400, Igor Sereda <sereda_at_spb.runnet.ru> wrote:

>I ran a test and learned that the value of sysdate is being calculated
>once. Is there a way to force Oracle to recalculate functions
>each time (for each row, heh? :)

It may that since SYSDATE has no arguments, that the optimizer has decided to call it once rather than once for each row. If you think about it, that's a sensible approach. You could probably force it to be called for each row by writing your own function to call sysdate, making sure to pass at least one argument. For example:

create or replace function sysdate_for_each_row(X in date) return date
as
begin
  return sysdate;
end;

Then write you query as follows:

	update table
		set X = sysdate_for_each_row(X);

Your function, of course, does nothing with the input parameter, but Oracle doesn't know that. The param should force oracle to call the function for each row that is updated.

One other thing to consider. SYSDATE resolves down to the second. It's very possible to update several rows in a one second period.

regards,

Jonathan Gennick Received on Thu May 07 1998 - 08:39:53 CDT