Re: What is "dual"

MarkP28665 wrote:
>
> SYS.DUAL is a dummy table. It exists and as stated by the original note
> has one row of one column.
>
> It is useful for lots of things. Oracle applications like Forms and
> Report Writer and developers using similiar products use dual to select
> dates and Oracle function values into application variables, i.e., select
> to_char(sysdate,'DD Month YYYY')
> into :variable from dual. And dual is useful for getting the values of
> the psuedo columns UID and USER.
>
> Dual is also useful in SQL*Plus for testing syntax and function nesting
> before coding them into embedded code.
>
> I have been told by Oracle support personell that dual is used internally
> by some Oracle products to accomplish populating data values like I said
> in my first paragraph.

When processing a SQL script to populate Oracle tables with info from our
mainframe DB2 system, I like to have a spool file which contains table name
and time as well as the information normally shown after executing the SQL.
Using 'dual' simplifies my SQL this:

-- 
select  '=== Table: my_oracle_table_1 -- '
     || to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
     from dual;
--
TRUNCATE TABLE my_oracle_table_1 REUSE STORAGE;
insert into my_oracle_table_1
 (my_oracle_columns)
  select my_mainframe_columns
    from my_mainframe_table;
commit;
--
etc.