Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle 11g Virtual Columns
CREATE TABLE o11_date_lut
(date_id       NUMBER NOT NULL,
 date_num      NUMBER(8) DEFAULT TO_CHAR(sysdate,'yyyymmdd') NOT NULL
                 CONSTRAINT pk_o11_date_lut PRIMARY KEY,
 month_num     AS (TO_NUMBER(TO_CHAR(TO_DATE(date_num,'yyyymmdd'),'mm'))),
 year_num      AS (TO_NUMBER(TO_CHAR(TO_DATE(date_num,'yyyymmdd'),'yyyy'))),
 month_desc    AS (TO_CHAR(TO_DATE(date_num,'yyyymmdd'),'Mon'))
);

INSERT INTO o11_date_lut
(date_id,
 date_num)
WITH q_date AS
(SELECT &i_val  val
 FROM DUAL)
SELECT 
 level,
 TO_CHAR(SYSDATE-q_date.val+level,'yyyymmdd') date_id
FROM  q_date
CONNECT BY level <= q_date.val;
COMMIT;

Oracle 11g Table With Virtual Columns

SELECT q_t.* 
FROM
(Select 
 ' '||column_name as " Name",
 DECODE(nullable,'N','NOT NULL','')  as "Null?",
 CAST(RTRIM(DECODE(data_type,
  'VARCHAR2',data_type||'('||data_length||')',
  'CHAR',data_type||'('||data_length||')',
  'NUMBER',data_type||
    DECODE(data_precision,NULL,'','('||data_precision||
     Decode(Nvl(Data_Scale,0),0,'',','||Data_Scale)||')'),
  Data_Type)) AS VARCHAR2(20)) As "Type"
FROM all_tab_columns
Where Owner = user
And   Table_Name = Upper('&p_table')
Order By Column_Id
) q_t;

Description - Oracle 11g Table With Virtual Columns

SELECT 
 date_id,
 date_num,
 year_num,
 month_num,
 CAST(month_desc AS VARCHAR2(15)) month_desc
FROM o11_date_lut;

Oracle 11g Table With Virtual Columns


Index 11gXEr2

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 24, 2014

  23426