DB GEEK

Hey geeks,
DUAL table is a very important table available in the Oracle database. It gets created automatically during the installation of the Oracle database and is available for all the users irrespective of their roles and privileges. SYS is the owner is this table all the users can access this table without mentioning the schema name.
Primarily, this table contains ONE COLUMN (DUMMY VARCHAR2(1)) and ONE ROW (value ‘X’) but the users can display multiple rows using this table and level clause.
It is also called a DUMMY table because of having a column named DUMMY.
So you might be thinking, if it has only one column and one row then why does this table exist in the database? Let me tell you, this table is used to test constants or functions by the developers in SQL Engine. As this table has only one column and one row so SELECT statement shows the result in a single row only without specifying a where clause.

Here are a few examples.

Example-1:
Selecting from DUAL table without any constant.

SELECT
    *
FROM
    DUAL;

Example-2:
Selecting from DUAL table with a constant.

SELECT
    'Follow me on Instagram!' as Insta
FROM
    DUAL;

Example-3:
Selecting from DUAL table with Functions.

SELECT
    'admin@dbgeek.in' as Email_Address,
    SUBSTR('admin@dbgeek.in',instr('admin@dbgeek.in','@')+1) as Email_Domain
FROM
    DUAL;

Example-4:
Last but not the least, selecting from DUAL table displaying multiple rows. Let’s display the first 5 alphabets.

select chr(64+level) as Alphabets
from dual
connect by level<=5; /*If we replace 5 with 26, it will generate 26 rows having values A to Z*/

That is all about DUAL table. Go ahead and smash the Oracle engine with your testing. Share your outputs on social media platforms and do not forget to tag DBGEEK.

If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share it with your friends and colleagues on your social media. For more updates join my Facebook group and do like my Facebook page.

Thank you,
Kapil Kumar

Sharing is caring!