usage of Dual table in oracle

[kiss07]

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)be tween two:?

pls explain..


ARUN..

[debasisdas]

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\RD BMS\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

select user,sysdate from dual;

SELECT CHR(78) FROM DUAL;

SELECT (319/212)+10 FROM DUAL;

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 .

[debasisdas]

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:

SELECT supplier_name, 
decode(supplier_id, 10000, 'HCL', 
 10001, 'SPA', 
 10002, 'SPIDER', 
  'SVNT') result 
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
     result := 'HCL';
ELSIF supplier_id = 10001 THEN
    result := 'SPA';
ELSIF supplier_id = 10002 THEN
    result := 'SPIDER';
ELSE
    result := 'SVNT';
END IF;

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 .

[imonline2007]

what is a pseudo column

[debasisdas]

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.

[Medhatithi]

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:

SELECT supplier_name, 
decode(supplier_id, 10000, 'HCL', 
 10001, 'SPA', 
 10002, 'SPIDER', 
  'SVNT') result 
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
     result := 'HCL';
ELSIF supplier_id = 10001 THEN
    result := 'SPA';
ELSIF supplier_id = 10002 THEN
    result := 'SPIDER';
ELSE
    result := 'SVNT';
END IF;

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(stat us,'BC',NULL,cr eate_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.