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; 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; SELECT date_id, date_num, year_num, month_num, CAST(month_desc AS VARCHAR2(15)) month_desc FROM o11_date_lut;
23426