Actual use of DUAL table in Oracle

 
My Tech Channel link:- https://www.youtube.com/oracleshooter
 
 
--====================================================
This is a Dummy Table in Oracle database
use:- to compute some mathematical calculation
use:- we can use some standard function with the help of dual table
use:- calculation from different-2 table
use:- pseudo columns
 
select * from DUAL
 
Table Name :- DUAL
Table Owner / Schema :- SYS
Column Name :- DUMMY
Column Type :- Varchar2(1)
Column default Value :- X
 
--====================================================
 sysdate
substr
instr
trim
ltrim
DESC DUAL
select * from dual
 
--====================================================
 
select * from all_objects
where OBJECT_NAME = 'DUAL'
 
--====================================================
Usages:- any system defined or user definined funation :- we can test by using DUAL table what actually it returns.
 
USER;
 
select USER from dual
 
SYSDATE
 
select SYSDATE from dual
 
select TO_DATE(SYSDATE) from dual
 
select SUBSTR (:PASS_YOUR_VALUE, 1, 5) from dual
 
--===================================================
 
create sequence XX_TEST_SEQ
 
XX_TEST_SEQ.NEXTVAL
 
select XX_TEST_SEQ.NEXTVAL from dual
 
select XX_TEST_SEQ.CURRVAL from dual
 
--===================================================
 
we can not use decode/case directly in any variable
for that we can use DUAL table.
 
DECLARE
V_A VARCHAR2(100);
 
BEGIN
 
V_A := DECODE ( :PASS_YOUR_VALUE , '1', 'ORACLE_SHOOTER' , 'HELLO');
dbms_output.put_line (V_A);
END;
 
 
DECLARE
V_A VARCHAR2(100);
 
BEGIN
 
select DECODE ( :PASS_YOUR_VALUE , '1', 'ORACLE_SHOOTER' , 'HELLO') INTO V_A from DUAL;
 
dbms_output.put_line (V_A);
END;
 
 
--===================================================
 
if you want to print some pattern
 
 
select rownum, substr( '*****',1, ROWNUM) A, substr( '*****', ROWNUM) B from dual CONNECT BY LEVEL  <= 5
 
 
ROWNUM          A          B
1          *          *****
2          **         ****
3          ***       ***
4          ****     **
5          *****   *
 
 
 
 
select rownum || '  *  5   = ', rownum*5  TABLE_OF_5  from dual CONNECT BY LEVEL  <= 10
 
 
TABLE_OF_5
1  *  5   =          5
2  *  5   =          10
3  *  5   =          15
4  *  5   =          20
5  *  5   =          25
6  *  5   =          30
7  *  5   =          35
8  *  5   =          40
9  *  5   =          45
10  *  5   =        50
 
 

--===Actual use of DUAL table  in Oracle ============