@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display view's query
*  Parameters : 1 - OWNER, this could also be passed as OWNER.view_name
*               2 - VIEW_NAME
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  15-May-12  Vishal Gupta  Intial version
*  02-Jul-12  Vishal Gupta  Modified to allow pass OWNER, VIEW_NAME either as separate input
*                           or as single input joined as owner.view_name 
*
*
*/

/************************************
*  INPUT PARAMETERS
************************************/
DEFINE OWNER="&&1"
DEFINE VIEW_NAME="&&2"



COLUMN  _owner       NEW_VALUE owner          NOPRINT
COLUMN  _view_name   NEW_VALUE view_name      NOPRINT

set term off
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END ) "_owner"
     , DECODE(UPPER('&&view_name'),'',SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1),UPPER('&&view_name')) "_view_name"
FROM DUAL;
set term on


Prompt 
Prompt   ************************************************************
Prompt   ** View &OWNER..&VIEW_NAME's Query Text
Prompt   ************************************************************


COLUMN text              HEADING "Query"          FORMAT a150

set long 400000
set pages 1000

SELECT TEXT
  FROM dba_views v
WHERE v.owner     = '&&owner' 
  AND v.view_name = '&&view_name' 
;

SELECT view_definition text
  FROM v$fixed_view_definition v
WHERE 'SYS'     = '&&owner' 
  AND v.view_name = '&&view_name' 
 
;   

UNDEFINE OWNER
UNDEFINE VIEW_NAME


@@footer