oracle cheat sheet

-- File:OracleCheatSheet.sql

-- DEFINE A NICE DATETIME FORMAT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD HH24:MI:SS';

-- FIND FIRST THE NAME OF THE TABLE 

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%RUN%'

; 

-- GENERATE A COLUMN SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_VAR_RUN'

;

-- GENERATE A TABLE_NAME.COLUMN_NAME SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || TABLE_NAME || '.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_VAR_RUN'

;

-- GET THE COLUMN LIST FROM A TABLE 

SELECT ',' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_RUN' ; 

-- convert ids to human readable values 

select 

-- /*

, T2.WORKFLOW_NAME 

, T2.START_TIME

, T2.END_TIME

, decode (RUN_STATUS_CODE,

1 , 'Succeeded',

2,  'Disabled',

3,  'Failed',

4,  'Stopped',

5,  'Aborted',

6,  'Running',

15, 'Terminated')  Status

from OPB_WFLOW_RUN T2

WHERE 1=1

AND START_TIME >= to_date('2012-04-03 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND END_TIME   <=   to_date('2012-04-04 00:00:00','YYYY-MM-DD HH24:MI:SS')

ORDER BY START_TIME DESC

; 

SELECT TABLE_NAME as TableName , COLUMN_NAME as ColumnName , FROM all_tab_columns ; 

/* Purpose: 

To provide a cheat sheet for oracle sql 

VersionHistory: 

1.1.0 --- ysg --- added generate col list 

1.0.0 --- ysg --- initial creation 

*/