@@header

set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Search for column_name
*  Parameters : 1 - OWNER        (% - wildchar, \ - escape char, Default value '%')
*               2 - table_name   (% - wildchar, \ - escape char, Default value '%')
*               3 - column_name  (% - wildchar, \ - escape char, Default value '%')
*               3 - ObjectType   (% - wildchar, \ - escape char, Default value '%')
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-04  Vishal Gupta  First Draft
*  02-May-12  Vishal Gupta  change input parameters
*  19-Jul-12  Vishal Gupta  Added object type as input parameter
*
*/
set term on


/************************************
*  INPUT PARAMETERS
************************************/
DEFINE owner="&&1"
DEFINE table_name="&&2"
DEFINE column_name="&&3"
DEFINE object_type="&&4"


set term off
COLUMN  _owner           NEW_VALUE owner          NOPRINT
COLUMN  _table_name      NEW_VALUE table_name     NOPRINT
COLUMN  _column_name     NEW_VALUE column_name    NOPRINT
COLUMN  _object_type     NEW_VALUE object_type    NOPRINT

SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
     , CASE WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) ELSE UPPER('&&table_name') END  "_table_name"
     , CASE INSTR('&&owner','.') WHEN 0 THEN UPPER('&&column_name') ELSE UPPER('&&table_name')  END    "_column_name"
     , CASE INSTR('&&owner','.') WHEN 0 THEN UPPER('&&object_type') ELSE UPPER('&&column_name')  END   "_object_type"
FROM DUAL
;

SELECT DECODE(UPPER('&&column_name'),'','%',UPPER('&&column_name'))                   "_column_name"
     , DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type'))                   "_object_type"
FROM DUAL
;

set term on



PROMPT *****************************************************************
PROMPT * COLUMNS LIST 
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - Owner       = '&&owner'
PROMPT *  - Object Name = '&&table_name'
PROMPT *  - Column Name = '&&column_name'
PROMPT *****************************************************************




COLUMN object_name   HEADING "ObjectName"          FORMAT a45
COLUMN object_type   HEADING "ObjectType"          FORMAT a17
COLUMN column_name   HEADING "ColumnName"          FORMAT a30
COLUMN Type          HEADING "ColumnType"          FORMAT a30
COLUMN nullable      HEADING "Null?"               FORMAT a8
COLUMN data_default  HEADING "Default|Value"       FORMAT a30



SELECT c.owner || '.' || c.table_name object_name
     , o.object_type
     , c.column_name
     , DECODE(c.nullable,'N','NOT NULL','') nullable
     , CASE 
          WHEN c.data_type = 'NUMBER'  
               THEN c.data_type || NVL2(c.data_precision||c.data_scale, '(' || NVL(c.data_precision,'38') || ',' || NVL(c.data_scale,'127') || ')','')
          WHEN c.data_type = 'FLOAT' 
               THEN c.data_type || NVL2(c.data_precision,'(' || c.data_precision || ')','')
          WHEN c.data_type = 'VARCHAR2' OR  c.data_type = 'CHAR' 
               THEN c.data_type || '(' || c.char_length || ' ' ||  DECODE(c.char_used,'B','BYTE','CHAR') || ')'
          WHEN c.data_type = 'NVARCHAR2' OR  c.data_type = 'NCHAR' 
               THEN c.data_type || '(' || c.char_length ||  ')'
          WHEN c.data_type = 'RAW' OR c.data_type = 'UROWID'
               THEN c.data_type || '(' || c.data_length || ')' 
          ELSE  c.data_type
       END  Type
FROM dba_tab_columns c
   , dba_objects o
WHERE o.owner = c.owner
  AND o.object_name = c.table_name
  AND o.SUBOBJECT_NAME IS NULL --Exclude paritions and sub-paritions
  AND UPPER(c.owner) like UPPER('&&owner') ESCAPE '\'
  AND UPPER(c.table_name) like UPPER('&&table_name') ESCAPE '\'
  AND UPPER(c.column_name) like UPPER('&&column_name') ESCAPE '\'
  AND UPPER(o.object_type) like UPPER('&&object_type') ESCAPE '\'
  AND c.table_name NOT LIKE 'BIN$%'
ORDER BY c.column_name,c.owner, c.table_name  ;

UNDEFINE owner
UNDEFINE table_name
UNDEFINE column_name
UNDEFINE object_type

@@footer