Re: select * from dual in MOUNTED database returns 4 columns (one row)

In article <1026200921.18484.0.nnrp-13.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>I can't remember when it came in, but when
>the database is not open, select * from dual
>is implemented by
> select * from x$dual;
>
>It's been around for AGES, and I can't think
>why all the stuff in standard.sql (such as the
>pl/sql USER, or UID calls) doesn't use it
>instead of incurring the logical I/O of using
>the proper DUAL table.
>
>

Again I can't say this for certain, but if it's anything like some of the wonderful applications I used to manage databases for years and years ago, it's purely and simply a case of it needs changing in too many places. Remember way back when (sometime in v6 from memory) when DUAL went from being owned by SYSTEM to being owned by SYS (at least IIRC that's the way it went)? We looked at the number of places we'd need to change application code and it was literally hundreds of screens. The standards at that site were to include the date and time in the top right of each screen, and each screen did that by querying SYSTEM.DUAL (hardcoded so we didn't have the supposed overhead of translating DUAL to who it belonged to). We ended up creating a synonym SYSTEM.DUAL instead of changing all the code. Same sort of thing for the Oracle code I suspect. You're probably never going to see a project "let's change every reference to DUAL to X$DUAL" in any Oracle release, but you may see that change made gradually as other projects work on those specific parts of the code.

Remember, I'm speculating here so I could be out by miles again! :)

Pete
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Next Seminars
> UK July / Sept
> Australia July / August
> Malaysia September
> USA (MI) November
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Richard Foote wrote in message ...
>>Hi Vsevolod,
>>
>>I must admit I've never tried to select from dual before with an unopened
>>database so I'm not sure what you are actually accessing (I suspect some
>>internal dual like object). What I do know is that you are not accessing
>the
>>dual table/synonym as this is only accessible with an open database so I
>>would strongly suspect they are not one and the same.
>>
>>Regards
>>
>>Richard
>>"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message
>>news:4f7d504c.0207082214.506816ea_at_posting.google.com...
>>> If a database is mounted (not opened), then
>>>
>>> select * from dual
>>>
>>> returns 4 columns (one row).
>>> However, when the database is opened,
>>>
>>> select * from dual
>>>
>>> returns one column only.
>>>
>>> I checked DBA_OBJECTS - there are only two objects with name DUAL,
>>> both in SYS schema, table and synonym.
>>>
>>> Thanks,
>>> Sev
>>
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue Jul 09 2002 - 12:01:48 CDT