OracleでDUAL表の使い方
1.現在ユーザを取得
select user from dual
—————————————– ——– —————————-
USER
STARTNEWS24USER88
2.システム時刻を取得
select to_char(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) from dual
—————————————– ——– —————————-
TO_CHAR(SYSDATE,’YYYY-MM-DDHH24:MI:SS’)
2016-08-25 10:31:14
select sysdate from dual
—————————————– ——– —————————-
SYSDATE
16-08-25
3.ロケール(locale)を取得
select SYS_CONTEXT('USERENV’,’language’) from dual;
—————————————– ——– —————————-
SYS_CONTEXT('USERENV’,’LANGUAGE’)
JAPANESE_JAPAN.JA16SJIS
4.ランダム数字を取得
select SYS.DBMS_RANDOM.RANDOM from dual
—————————————– ——– —————————-
RANDOM
167899999
5.電卓として利用
select 2*3 from dual
—————————————– ——– —————————-
2*3
6.dualのオブジェクト名とオブジェクトタイプを取得
select owner, object_name , object_type from dba_objects where object_name like '%DUAL%’; OWNER OBJECT_NAME OBJECT_TYPE
—————————————– ——– —————————-
OWNER OBJECT_NAME OBJECT_TYPE
SYS DUAL TABLE
PUBLIC DUAL SYNONYM
select dummy from dual
—————————————– ——– —————————-
DUMMY
X
7.DUALの英語ドキュメント
There is internalized code that makes this happen. Code checks that ensure that a table scan of SYS.DUAL only returns one row. Svrmgrl behaviour is incorrect but this is now an obsolete product. The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1). This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other prebuilt or application functions. If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception. So DUAL should ALWAYS have 1 and only 1 row.