Re: SYSDATE

Hello Erik,
your DBA is right in so far as you can shorten the UPDATE statement the following way:
update <table_name>
set date_field=SYSDATE
where <some condition>;

That means that you don't need a select statement for SYSDATE. As far as I could draw from my handbooks SYSDATE is a function.

The description for a pseudo-column is that a pseudo-column is a column that does not exist in a table but that you can treat as if it would. That means you can select it (from DUAL), you can use it in where clauses etc. (Only exception: You can't update it.) I think this description makes SYSDATE a pseudo-column. Another example for a pseudo column is the ROWID. The ROWID makes a row unique for the system.

DUAL is a dummy table. (Try "select * from dual;"!) As you can't call system functions like SYSDATE as you're normally used to (call function (...);) the function is "selected" from this dummy table. This is done automatically. Only if you want to have the SYSDATE without any other contents you can select it from the DUAL table.

Hope these informations help you
Best regards
Corinna Becker

Erik Bowe <ebowe_at_SPSU.edu> wrote in article <01bd077a$12f51ca0$9af445cf_at_ebowe>...
>
> Got a quick question on SYSDATE...
>
> I'm currently working on project where we are migrating some Pro*C, UNIX
> processes to make them fully Oracle 7 compliant by removing "workarounds"
> that were necessary in Oracle 6. One of the major changes is converting
 a
> number of UPDATE statements that read as follows:
>
> UPDATE <tablename>
> SET <date field 1> = (SELECT SYSDATE FROM DUAL),
> <date field 2> = (SELECT SYSDATE FROM DUAL),
> ....
>
> WHERE <some condition>
>
> ...to just using SYSDATE without the SELECT.
>
> Unfortunately, I'm being told by a DBA that by just putting SYSDATE, I
> won't gain anything but readability in the code and that internally
 Oracle
> 7 is SELECTing SYSDATE from the DUAL table when used in this context.
 They
> go on further to say that SYSDATE is actually a pseudo-column.
>
> As I understand it, SYSDATE is a function and not a pseudo-column and by
> using it WITHOUT the SELECT, it should run a little quicker
 (unfortunately
> I haven't had time to test if that's the case).
>
> Can anyone tell me as to what's really going on with SYSDATE within the
> context of UPDATE??? Is it a function? ...or is it being translated by
> Oracle behind the scenes to a sub-query within the UPDATE?
>
> Any help would be appreciated!!!
>
> Thanks, Erik
> ebowe_at_SPSU.edu
>
  Received on Mon Dec 15 1997 - 00:00:00 CST