By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,016 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,016 IT Pros & Developers. It's quick & easy.

usage of Dual table in oracle

kiss07
P: 99
Hi deba,

What is the usage of dual table in oracle?Use any applications?
what is difference between decode and if-the -else(any restrictions)between two:?

pls explain..


ARUN..
May 5 '07 #1
Share this Question
Share on Google+
5 Replies


debasisdas
Expert 5K+
P: 8,127
Hi,

Actually what happens when we issue create database command, Oracle automatically creates the tablespace and tables using the sql.bsq script found in the $oracle_home/rdbms/admin directory (oracle\product\10.2.0\db_1\RDBMS\ADMIN------in 10g)this scripts the dual table and is owned by the sys and has one column and one row.

Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

The owner of dual is SYS but dual can be accessed by every user.

As dual contains exactly one row (unless someone manipulates it), it is guaranteed to return exactly one row in select statements.
Therefor, dual is the prefered table to select a pseudo column (such as sysdate
Expand|Select|Wrap|Line Numbers
  1. select user,sysdate from dual;
  2.  
  3. SELECT CHR(78) FROM DUAL;
  4.  
  5. SELECT (319/212)+10 FROM DUAL;
  6.  
  7.  
Although it is possible to delete the one record, or insert additional records, one really should not do that!.

U can use any predefined function from this table.
Also if user creates any function that can also be used with the help of this DUAL table .
May 5 '07 #2

debasisdas
Expert 5K+
P: 8,127
This is regarding your second question--DECODE ver IF-THEN-ELSE
----------------------------------------------------------------------------------------------------------
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

Expression is the value to compare.

Search is the value that is compared against expression.

Result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

For Example:

You could use the decode function in an SQL statement as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT supplier_name, 
  2. decode(supplier_id, 10000, 'HCL', 
  3.  10001, 'SPA', 
  4.  10002, 'SPIDER', 
  5.   'SVNT') result 
  6. FROM suppliers; 
  7.  
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
Expand|Select|Wrap|Line Numbers
  1. IF supplier_id = 10000 THEN
  2.      result := 'HCL';
  3. ELSIF supplier_id = 10001 THEN
  4.     result := 'SPA';
  5. ELSIF supplier_id = 10002 THEN
  6.     result := 'SPIDER';
  7. ELSE
  8.     result := 'SVNT';
  9. END IF;
  10.  
The decode function will compare each supplier_id value, one by one.

The main difference is Decode can be used directly in SQL where as
IF-THEN-ELSE
being a structure can only be used in PL/SQL

You can use CASE instead of if-then

Performance wise case is faster than if-then and can be used in SQL .
May 5 '07 #3

imonline2007
P: 1
what is a pseudo column
May 7 '07 #4

debasisdas
Expert 5K+
P: 8,127
As u know the word pseudo means virtual

so pseudo column means a column which doesnot exist physically bust still can be used.

For alist of pseudo columns follow the Link

and go to the first post in the tread at the bottom of the page.
May 7 '07 #5

P: 33
This is regarding your second question--DECODE ver IF-THEN-ELSE
----------------------------------------------------------------------------------------------------------
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

Expression is the value to compare.

Search is the value that is compared against expression.

Result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

For Example:

You could use the decode function in an SQL statement as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT supplier_name, 
  2. decode(supplier_id, 10000, 'HCL', 
  3.  10001, 'SPA', 
  4.  10002, 'SPIDER', 
  5.   'SVNT') result 
  6. FROM suppliers; 
  7.  
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
Expand|Select|Wrap|Line Numbers
  1. IF supplier_id = 10000 THEN
  2.      result := 'HCL';
  3. ELSIF supplier_id = 10001 THEN
  4.     result := 'SPA';
  5. ELSIF supplier_id = 10002 THEN
  6.     result := 'SPIDER';
  7. ELSE
  8.     result := 'SVNT';
  9. END IF;
  10.  
The decode function will compare each supplier_id value, one by one.

The main difference is Decode can be used directly in SQL where as
IF-THEN-ELSE
being a structure can only be used in PL/SQL

You can use CASE instead of if-then

Performance wise case is faster than if-then and can be used in SQL .


There is however, a very important point regarding decode. It automatically converts the second return value to the datatype of the first return value. And if the first return value is null, then the second return value is converted to varchar2. BE VERY CAREFUL USING DECODE FUNCTION IF THE FIRST RETURN VALUE IS NULL i.e.
max(decode(status,'BC',NULL,create_date))
In this case, the create_date column will be converted to varchar2 type, and so
the max may give errors (we faced this issue just a month ago)
CASE-WHEN is obviously a better choice in this regard.
Jun 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.