69 lines
1.7 KiB
SQL
69 lines
1.7 KiB
SQL
@@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
|