104 lines
3.1 KiB
MySQL
104 lines
3.1 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display view's query
|
|
* Parameters : 1 - Search Text (% - wildchar, \ - escape char)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-May-12 Vishal Gupta Initial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE OWNER
|
|
UNDEFINE view_name
|
|
UNDEFINE searchtext
|
|
|
|
DEFINE OWNER="&&1"
|
|
DEFINE view_name="&&2"
|
|
DEFINE searchtext="&&3"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _view_name NEW_VALUE view_name NOPRINT
|
|
COLUMN _searchtext NEW_VALUE searchtext 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"
|
|
, UPPER('&&searchtext') "_searchtext"
|
|
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************
|
|
PROMPT * View Definition Search Results
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - View Name = '&&view_name'
|
|
PROMPT * - Search Criteria = '&&searchtext'
|
|
PROMPT *****************************************************
|
|
|
|
|
|
|
|
COLUMN text HEADING "Query" FORMAT a150
|
|
|
|
set long 400000
|
|
set pages 1000
|
|
|
|
--------------------------------------------------------------------
|
|
-- Had to use XML as pattern matching does not work on LONG datatype
|
|
-- Also there is no direct function convert LONG to VARCHAR2
|
|
--------------------------------------------------------------------
|
|
select *
|
|
from
|
|
xmltable( '/ROWSET/ROW'
|
|
passing dbms_xmlgen.getXMLType(q'[SELECT v.owner
|
|
, v.view_name
|
|
, v.text
|
|
FROM dba_views v
|
|
WHERE v.owner like '&&owner'
|
|
AND v.view_name like '&&view_name'
|
|
]'
|
|
)
|
|
columns
|
|
owner varchar2(30)
|
|
, view_name varchar2(30)
|
|
, text varchar2(4000)
|
|
) v2
|
|
WHERE 1=1
|
|
AND v2.owner like '&&owner' ESCAPE '\'
|
|
AND v2.view_name like '&&view_name' ESCAPE '\'
|
|
AND UPPER(v2.TEXT) like upper('&&searchtext') ESCAPE '\'
|
|
;
|
|
|
|
|
|
|
|
SELECT 'SYS' owner
|
|
, v.view_name
|
|
, v.view_definition text
|
|
FROM v$fixed_view_definition v
|
|
WHERE 1=1
|
|
AND 'SYS' like '&&owner' ESCAPE '\'
|
|
AND v.view_name like '&&view_name' ESCAPE '\'
|
|
AND UPPER(v.view_definition) like upper('&&searchtext') ESCAPE '\'
|
|
;
|
|
|
|
|
|
@@footer
|