ORACLE中如何查找特定对象中的文本内容~
[c-sharp] view plaincopy
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO2','SCOTT') text from dual; 
 
TEXT 
---------------------------------------- 
 
  CREATE OR REPLACE PROCEDURE "SCOTT"."P 
RO2" 
is 
begin 
dbms_output.put_line('wangpeng up'); 
end; 
 
 
 
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO1','SCOTT') text from dual; 
 
TEXT 
---------------------------------------- 
 
  CREATE OR REPLACE PROCEDURE "SCOTT"."P 
RO1" 
is 
begin 
dbms_output.put_line('wanghai up'); 
end; 
 
SQL> select * from( 
  2  SELECT NAME , LINE , 'Create or Replace ' || TEXT as text 
  3    FROM USER_SOURCE 
  4  WHERE TYPE = 'PROCEDURE' 
  5    AND LINE = 1 
  6  UNION 
  7  SELECT NAME, LINE, TEXT as text 
  8    FROM USER_SOURCE 
  9  WHERE TYPE = 'PROCEDURE' 
10    AND LINE > 1 
11  UNION 
12  SELECT NAME, 999999 , '/' as text 
13    FROM USER_SOURCE 
14  WHERE TYPE = 'PROCEDURE' 
15    AND LINE = 1 
16  ORDER BY 1, 2 
17  ) c 
18  where UPPER(c.text) like '%WANGHAI%'; 
 
NAME          LINE TEXT 
---------- ------- ---------------------------------------- 
PRO1             4 dbms_output.put_line('wanghai up'); 
 
SQL> select * from( 
  2  SELECT NAME , LINE , 'Create or Replace ' || TEXT as text 
  3    FROM USER_SOURCE 
  4  WHERE TYPE = 'PROCEDURE' 
  5    AND LINE = 1 
  6  UNION 
  7  SELECT NAME, LINE, TEXT as text 
  8    FROM USER_SOURCE 
  9  WHERE TYPE = 'PROCEDURE' 
10    AND LINE > 1 
11  UNION 
12  SELECT NAME, 999999 , '/' as text 
13    FROM USER_SOURCE 
14  WHERE TYPE = 'PROCEDURE' 
15    AND LINE = 1 
16  ORDER BY 1, 2 
17  ) c 
18  where UPPER(c.text) like '%WANGPENG%'; 
 
NAME          LINE TEXT 
---------- ------- ---------------------------------------- 
PRO2             4 dbms_output.put_line('wangpeng up'); 
 
SQL> select * from( 
  2  SELECT NAME , LINE , 'Create or Replace ' || TEXT as text 
  3    FROM USER_SOURCE 
  4  WHERE TYPE = 'PROCEDURE' 
  5    AND LINE = 1 
  6  UNION 
  7  SELECT NAME, LINE, TEXT as text 
  8    FROM USER_SOURCE 
  9  WHERE TYPE = 'PROCEDURE' 
10    AND LINE > 1 
11  UNION 
12  SELECT NAME, 999999 , '/' as text 
13    FROM USER_SOURCE 
14  WHERE TYPE = 'PROCEDURE' 
15    AND LINE = 1 
16  ORDER BY 1, 2&nb