A question recently came into an oracle French forum (http://www.developpez.net/forums/d1117051/bases-donnees/oracle/source-vue-format-text/) asking about how to get a DDL of views containing a given text
I thought it is worth writing here the English translation of my French answer to that thread. I will start by creating a view in which I will use the term ‘Mohamed’
mhouri.world> create view view_test as 2 select * 3 from emp 4 where ename = 'Mohamed'; View created.
And now we would like to get views that contains the text ‘Mohamed’ in their DDL creation script.
mhouri.world> SELECT *
2 FROM
3 (SELECT
4 dbms_metadata.get_ddl('VIEW', view_name) txt
5 FROM user_views
6 ) ww
7 WHERE
8 dbms_lob.instr (ww.txt, 'Mohamed') != 0
9 ;
CREATE OR REPLACE FORCE VIEW "MHOURI"."VIEW_TEST" ("EMPNO", "ENAME", "JOB", "MGR
", "HIREDATE", "SAL", "COMM", "DEPTNO") AS
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from emp
where ename = 'Mohamed'
Hope this will help.
Advertisement
