@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Generate Object's DDL
*  Parameters : 1 - owner          (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
*               2 - Object name    (% - wildchar, \ - escape char)
*               3 - SubObject name (% - wildchar, \ - escape char)
*               4 - Object Type    (% - wildchar, \ - escape char)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Mar-13  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
set pages 1000
set long 5000

UNDEFINE owner
UNDEFINE object_name
UNDEFINE subobject_name
UNDEFINE object_type

DEFINE owner="&&1"
DEFINE object_name="&&2"
DEFINE subobject_name="&&3"
DEFINE object_type="&&4"


COLUMN  _owner           NEW_VALUE owner            NOPRINT
COLUMN  _object_name     NEW_VALUE object_name      NOPRINT
COLUMN  _subobject_name  NEW_VALUE subobject_name   NOPRINT
COLUMN  _object_type     NEW_VALUE object_type      NOPRINT

set term off

SELECT DECODE(UPPER('&&owner'),'','%','&&owner')                   "_owner"
     , DECODE(UPPER('&&object_name'),'','%','&&object_name')       "_object_name"
     , DECODE(UPPER('&&subobject_name'),'','%','&&subobject_name') "_subobject_name"
     , DECODE(UPPER('&&object_type'),'','%','&&object_type')       "_object_type"
FROM DUAL;

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 DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name')) 
       END    "_object_name"
     /*, CASE  
            WHEN INSTR('&&owner','.') != 0 THEN DECODE(UPPER('&&object_name'),'','%',UPPER('&&object_name')) 
            ELSE DECODE(UPPER('&&object_type'),'','%',UPPER('&&object_type'))        
       END  "_object_type"
 */   
FROM DUAL;
set term on


PROMPT *****************************************************************
PROMPT * DDL
PROMPT * 
PROMPT * Input Parameters 
PROMPT *  - Owner          = '&&owner'
PROMPT *  - Object Name    = '&&object_name'
PROMPT *  - SubObject Name = '&&subobject_name'
PROMPT *  - Object Type    = '&&object_type'
PROMPT *****************************************************************


select dbms_metadata.get_ddl('&&object_type','&&object_name','&&owner') from dual;


@@footer