61 lines
1.3 KiB
SQL
61 lines
1.3 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display view's query
|
|
* Parameters : 1 - OWNER
|
|
* 2 - VIEW_NAME
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-May-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* 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
|
|
Prompt ************************************************************
|
|
|
|
COLUMN query HEADING "Query" FORMAT a150
|
|
|
|
set long 400000
|
|
set pages 1000
|
|
|
|
|
|
SELECT m.query
|
|
FROM dba_mviews m
|
|
WHERE m.owner = upper('&&OWNER')
|
|
AND m.mview_name = upper('&&VIEW_NAME')
|
|
;
|
|
|
|
UNDEFINE OWNER
|
|
UNDEFINE VIEW_NAME
|
|
|
|
|
|
@@footer
|