批量获取视图DDL

declare
 v_text clob;
 v_owner varchar2(30);
 v_view_name varchar2(30);
 v_count number;
 v_loc number;
 v_loc_s number;
 v_len number;
 cursor c_xv is
  select xv.owner,xv.view_name from xt_vwqd xv,all_views av
   where xv.owner=av.owner and xv.view_name=av.view_name
    and xv.owner ='&USER' and (xv.bbbz like '1%' or xv.bbbz like '2%');
 fhandle utl_file.file_type;
-- select av.view_name,av.owner from all_views av where av.owner='J1_DW' and av.view_name='V_SB_QYSDSNDSSYHQKMXB' 
begin
 for cur in c_xv loop
 v_loc_s :=1;
 v_owner :=cur.owner;
 v_view_name :=cur.view_name;
 SELECT length(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner))-length(replace(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),chr(10),'')) 
  into v_count
  FROM dual;
-- dbms_output.put_line(v_count);
 SELECT length(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner)) into v_len FROM dual;
 fhandle := utl_file.fopen(v_owner||'_FILE_DIR',v_view_name||'.sql', 'w',32767);
 for i in 1..v_count loop
--  dbms_output.put_line(i);
  select instr(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),chr(10),1,i) into v_loc from dual;
  SELECT substr(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),v_loc_s,v_loc-v_loc_s) INTO V_TEXT from dual;
--  SELECT substr(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),chr(10),1,1),1,instr(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),chr(10),1,1)) INTO V_TEXT from dual;
  utl_file.put_line(fhandle ,replace(v_text,chr(10),''));
--  dbms_output.put_line(replace(v_text,chr(10),''));
  v_loc_s :=v_loc;
 end loop;
 SELECT substr(dbms_metadata.get_ddl('VIEW',v_view_name,v_owner),v_loc_s,v_len-v_loc_s+1) INTO V_TEXT from dual;
 utl_file.put_line(fhandle ,replace(replace(v_text,';','')||';',chr(10),''));
 utl_file.fclose(fhandle);
-- dbms_output.put_line(replace(replace(v_text,';','')||';',chr(10),''));
 end loop;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-1770227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29802484/viewspace-1770227/