Re: SELECT ... FROM DUAL ?

Phil,

You can do it with SUBSTR, but not with SYSDATE (I am not sure about USER). When Oracle encounters SYSDATE, it will run SELECT SYSDATE FROM DUAL anyway. I ran into it when I tried to create a connectionless Oracle form, and code SYSDATE in it. The form would not run for the above reason - it needed a connection to execute SYSDATE. After some thinking I admitted that this is logically correct because it helps to
syncronize applications in time - all get the time from the server.

This behaviour must be specific for the PLSQL engine itself, that's why you

get the same on the server. I can see no reason why Oracle cannot execute SYSDATE without DUAL on the server.

Regards,
Karen Abgarian.

pbolduc_at_my-deja.com wrote:

> I have seen PL/SQL code (see
> http://www.oracle.com/oramag/code/cod02189.html) such as:
>
> SELECT SUBSTR (vissuedt, 3, 3) INTO vmonth FROM dual;
>
> my question is: would not this be more efficient written as:
>
> vmonth := SUBSTR(vissuedt,3,3);
>
> Due to my understanding there is a small, but definate cost from
> switching from PL/SQL context to SQL context. I understand with things
> like sequences need to be selected from DUAL.
>
> PL/SQL code I have seen and wanted to change right away:
>
> SELECT USER INTO v_username FROM dual;
>
> SELECT TRUNC(SYSDATE) INTO v_today FROM DUAL;
>
> Perhaps I am just being picky.
>
> Phil Bolduc
> North Vancouver, Canada
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Sep 17 1999 - 10:00:49 CDT