This content has been marked as final. Show 8 replies
Is it viable to create a table within a database called 'dual' with one column?It is possible. In any other than SYS schema.
CREATE TABLE dual (USER_COLUMN VARCHAR2(1))
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,
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
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....?
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 (
Also if this was done, would it have any affects on the database?
Why? Just use the DUAL table that already exists.
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.
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
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.