8 Replies Latest reply: Feb 4, 2013 10:46 AM by 6363 RSS

    A little more information on DUAL table than google provides.

    988996
      Greetings,

      I'm currently working on a defect on my program that populates a drop down with all the columns of a table when a table is selected. One of the options is to use the dual table; however, since dual table is a system table it doesn't "exist" in the database and therefor doesn't populate the for mentioned drop down with the column name available (dummy in this case). In the DB2 version of the software they create a table called dual and it works as is, but it seems to me that the dual table is handled quite differently in Oracle than in DB2.

      So to the question:

      Is it viable to create a table within a database called 'dual' with one column?

      CREATE TABLE dual (
      USER_COLUMN VARCHAR2(1))


      Also if this was done, would it have any affects on the database?


      Thanks in advance.

      Edited by: 985993 on Feb 4, 2013 7:36 AM
        • 1. Re: A little more information on DUAL table than google provides.
          Osama_Mustafa
          dual Is already exists table
          SQL> create table dual ( test varchar2(20));
          create table dual ( test varchar2(20))
                       *
          ERROR at line 1:
          ORA-00955: name is already used by an existing object
          • 2. Re: A little more information on DUAL table than google provides.
            SomeoneElse
            since dual table is a system table it doesn't "exist" in the database
            Eh? What do you mean by this?

            What source are you using to populate your list?
            • 3. Re: A little more information on DUAL table than google provides.
              Mark Malakanov (user11181920)
              Is it viable to create a table within a database called 'dual' with one column?
              CREATE TABLE dual (USER_COLUMN VARCHAR2(1))
              It is possible. In any other than SYS schema.
              Also if this was done, would it have any affects on the database?
              Effect on applications that work in scope of that schema where is custom DUAL may be huge and dangerous!

              If application relies on SYS.DUAL table but does not specify SYS schema,
              like
              SELECT some_col from DUAL left join SOME_TABLE on 1=1;

              If DUAL here is resolved to SYS.DUAL it will return at least one row
              If DUAL is resolved to YOUR_SCHEMA.DUAL and your dual has row number different than 1 result will be different.

              Also if query addresses DUAL.DUMMY column, and your local DUAL table does not have DUMMY the SQL will fail.
              In the DB2 version of the software they create a table called dual and it works as is, but it seems to me that the dual table is handled quite differently in Oracle than in DB2.
              if your custom DUAL is exactly same as Oracle's SYS.DUAL, there should not be any harm, but no benefit too :) .

              Edited by: Mark Malakanov (user11181920) on Feb 4, 2013 11:30 AM
              • 4. Re: A little more information on DUAL table than google provides.
                Osama_Mustafa
                Also Post the output of
                select sysdate from dual
                • 5. Re: A little more information on DUAL table than google provides.
                  Aman....
                  Dual is an internal, system table. So it's highly recommended that you don't mess it up as this can be a really serious damage to your Oracle db.

                  Read this thread,
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1562813956388

                  Aman....
                  • 6. Re: A little more information on DUAL table than google provides.
                    Richard Harrison .
                    Hi,
                    I once worked on a system that worked in a similar way - it used to do 'select * from user_tables' to create the drop down list. The way we 'fiddled' it was to have a view called USER_TABLES owned by the schema you were connecting as rather than the sys owned view (the local object takes precendence). Might work for you but you need to know what SQL it is running and know how the application behaves elsewhere so you don;t affect any other areas. Might be a solution....?

                    Cheers,
                    Harry
                    • 7. Re: A little more information on DUAL table than google provides.
                      rp0428
                      Welcome to the forum!

                      Whenever you post provide your 4 digit Oracle version.
                      >
                      I'm currently working on a defect on my program that populates a drop down with all the columns of a table when a table is selected.
                      >
                      So far so good.
                      >
                      One of the options is to use the dual table; however, since dual table is a system table it doesn't "exist" in the database and therefor doesn't populate the for mentioned drop down with the column name available (dummy in this case).
                      >
                      What? Of course the dual table exists in the database. You are misinformed. If it "doesn't populate ..." then you have a bug in your code that you need to find and fix.
                      >
                      In the DB2 version of the software they create a table called dual and it works as is, but it seems to me that the dual table is handled quite differently in Oracle than in DB2.
                      >
                      DB2 does not have a table called 'DUAL'. But when people port code from Oracle to DB2 sometimes that code uses the DUAL table. So rather than fix the code people try to take a shortcut and create a DUAL table to fool the code.
                      >
                      So to the question:

                      Is it viable to create a table within a database called 'dual' with one column?

                      CREATE TABLE dual (
                      USER_COLUMN VARCHAR2(1))

                      Also if this was done, would it have any affects on the database?
                      >
                      Why? Just use the DUAL table that already exists.
                      SQL> desc dual
                       Name                                      Null?    Type
                       ----------------------------------------- -------- ----------------------------
                      
                       DUMMY                                              VARCHAR2(1)
                      SQL>
                      
                      select owner, table_name, column_name, data_type, nullable
                      from all_tab_cols where table_name = 'DUAL'
                      
                      OWNER     TABLE_NAME     COLUMN_NAME     DATA_TYPE     NULLABLE
                      SYS     DUAL     DUMMY     VARCHAR2     Y
                      As you can see the ALL_TAB_COLS view shows the DUAL table. It is owned by SYS. That view lists ALL tables that the user has access to and not just the tables that the user owns.

                      You are likely querying the USER_TAB_COLS view which only shows the columns for tables that the user owns. That is what that view is supposed to do.

                      Modify you code to query the proper views and you don't need to create your own DUAL table.
                      • 8. Re: A little more information on DUAL table than google provides.
                        6363
                        I am still wondering why on earth anyone would want to see dual and the dummy column in a drop down anyway and what use it would be.