Systems Engineering and RDBMS

DUAL Table

Posted by decipherinfosys on July 6, 2007

Anyone who has used Oracle as their RDBMS for development would be aware of the DUAL table in Oracle. It is a single column/single row table to evaluate constant expression in the SELECT statements. It is owned by the SYS user but is accessible to all users. With this introduction, we would like to draw an attention to a very unusual case which just tells us that don’t take anything for granted.

Recently, at one of our client site, we came across a situation where they had created a DUAL table in the user schema and the reason why they did that was because their DBA was trying to migrate the application from Oracle to SQL Server and since SQL Server does not have a DUAL table concept, in order to keep the code the same, he introduced a physical table by the same name and it was created in the data-model and used in both Oracle and SQL Server. Needless to state, that this was changed as soon as we found it but let’s talk about the issues that it created in the production environment. When the DUAL table exists in the user schema, it takes precedence over the DUAL table in the SYS schema. Let us now examine what happens in such a case.

In the normal situation, where the DUAL table exists only in the SYS schema (which should be the case), the following SQL will display current system date.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
———
15-JUN-07

Now let us try to create DUAL table in user schema and issue the same statement again.

SQL> CREATE TABLE DUAL(COL1 VARCHAR(1));

Table created.

SQL> SELECT SYSDATE FROM DUAL;

no rows selected

Since there is no record in the physical USER Schema DUAL table, the select statement does not return any data.

Now let us try to take an export dump of the schema.

C:\Decipher>exp userID=decipher/decipher@orcl file=exp.dmp

This will also result into following error.

EXP-00002: error in writing to export file
EXP-00000: Export terminated unsuccessfully

Empty dual table in the USER schema does not allow us to perform export/import. This is not a very good situation where we have no clue that the export is failing because of missing record in DUAL table. Now let us insert a record into the table and re-issue the select statement.

SQL> INSERT INTO DUAL VALUES(‘X’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
———
15-JUN-07

If we issue the export command again, it will go through this time. Taking the example a little further, let us add one more record to dual table and see the results.

SQL> INSERT INTO DUAL VALUES(‘X’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
———
15-JUN-07
15-JUN-07

The code that is written under the assumption that the DUAL table only contains one row, any constant/expression evaluated against DUAL will fail in the above scenario. Wherever we have used this statement (in procedure/function etc.) to store value in local variable, statement will fail.

This is just to show the implication of the existence of a DUAL table in the user schema. If you have such a table (legacy system that you have inherited – we explained above why this client’s DBA had done it), then get rid of it since it is not required in the USER schemas.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: