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;
v_text_len number;
-- v_count_s number;
-- v_count_t number;
cursor c_xv is
select av.owner,av.view_name from all_views av
where av.owner ='&USER' /*and av.view_name='V_SB_CWBB_QYKJZZYBQY_SYZQY'*/;
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_1 in c_xv loop
v_owner :=cur_1.owner;
v_view_name :=cur_1.view_name;
fhandle := utl_file.fopen(v_owner||'_VIEW_DIR','init-view.sql','a');
if v_owner='J1_G3_ZBQ' then
--lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
v_text := '@"01 dw\01 inf\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_LDM' then
v_text := '@"01 dw\02 ldm\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DW' then
v_text := '@"01 dw\03 dw\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DI' then
v_text := '@"01 dw\05 di\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_CXTJ' then
v_text := '@"03 xt\01 cxtj\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DMT' then
v_text := '@"03 xt\06 dmt\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_METAONE' then
v_text := '@"03 xt\07 metaone\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_LDCX' then
v_text := '@"01 dw\08 ldcx\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='HX_ZGXT' then
v_text := '@"01 dw\06 hx_zgxt\01 object\02 view\'||v_view_name||'.sql";';
end if;
--@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
utl_file.put_line(fhandle ,v_text);
utl_file.fclose(fhandle);
end loop;
--
for cur in c_xv loop
v_loc_s :=0;
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||'_VIEW_DIR',v_view_name||'.sql', 'w',32767);
utl_file.put_line(fhandle ,'Prompt view '||v_owner||'.'||v_view_name||' created');
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+1,v_loc-v_loc_s) INTO V_TEXT from dual;
select length(v_text)/*,trunc(length(v_text)/2498)*/ into v_text_len/*,v_count_s*/ from dual;
-- dbms_output.put_line(v_loc);
-- 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;
if v_text=chr(10) then
utl_file.put_line(fhandle ,replace(v_text,chr(10),'--'));
else
if v_text_len>2498 then
utl_file.put_line(fhandle ,replace(replace(v_text,chr(10),''),chr(44),chr(44)||chr(10)));
/*
if v_text_len>2498 then
for j in 1..v_count_s loop
v_count_t :=1+2498*(j-1);
utl_file.put_line(fhandle ,substr(v_text,v_count_t,2498));
-- dbms_output.put_line(v_text_len||' '||v_count_t||' '||v_count_f);
end loop;
/*
if mod(v_text_len,2498)<>0 then
utl_file.put_line(fhandle ,replace(substr(v_text,2498*v_count_s+1,v_text_len-2498*v_count_s),chr(10),''));
end if;
*/
else
utl_file.put_line(fhandle ,replace(v_text,chr(10),''));
end if;
end if;
-- 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;
/
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;
v_text_len number;
-- v_count_s number;
-- v_count_t number;
cursor c_xv is
select av.owner,av.view_name from all_views av
where av.owner ='&USER' /*and av.view_name='V_SB_CWBB_QYKJZZYBQY_SYZQY'*/;
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_1 in c_xv loop
v_owner :=cur_1.owner;
v_view_name :=cur_1.view_name;
fhandle := utl_file.fopen(v_owner||'_VIEW_DIR','init-view.sql','a');
if v_owner='J1_G3_ZBQ' then
--lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
v_text := '@"01 dw\01 inf\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_LDM' then
v_text := '@"01 dw\02 ldm\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DW' then
v_text := '@"01 dw\03 dw\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DI' then
v_text := '@"01 dw\05 di\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_CXTJ' then
v_text := '@"03 xt\01 cxtj\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_DMT' then
v_text := '@"03 xt\06 dmt\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_METAONE' then
v_text := '@"03 xt\07 metaone\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='J1_LDCX' then
v_text := '@"01 dw\08 ldcx\01 object\02 view\'||v_view_name||'.sql";';
elsif v_owner='HX_ZGXT' then
v_text := '@"01 dw\06 hx_zgxt\01 object\02 view\'||v_view_name||'.sql";';
end if;
--@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
utl_file.put_line(fhandle ,v_text);
utl_file.fclose(fhandle);
end loop;
--
for cur in c_xv loop
v_loc_s :=0;
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||'_VIEW_DIR',v_view_name||'.sql', 'w',32767);
utl_file.put_line(fhandle ,'Prompt view '||v_owner||'.'||v_view_name||' created');
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+1,v_loc-v_loc_s) INTO V_TEXT from dual;
select length(v_text)/*,trunc(length(v_text)/2498)*/ into v_text_len/*,v_count_s*/ from dual;
-- dbms_output.put_line(v_loc);
-- 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;
if v_text=chr(10) then
utl_file.put_line(fhandle ,replace(v_text,chr(10),'--'));
else
if v_text_len>2498 then
utl_file.put_line(fhandle ,replace(replace(v_text,chr(10),''),chr(44),chr(44)||chr(10)));
/*
if v_text_len>2498 then
for j in 1..v_count_s loop
v_count_t :=1+2498*(j-1);
utl_file.put_line(fhandle ,substr(v_text,v_count_t,2498));
-- dbms_output.put_line(v_text_len||' '||v_count_t||' '||v_count_f);
end loop;
/*
if mod(v_text_len,2498)<>0 then
utl_file.put_line(fhandle ,replace(substr(v_text,2498*v_count_s+1,v_text_len-2498*v_count_s),chr(10),''));
end if;
*/
else
utl_file.put_line(fhandle ,replace(v_text,chr(10),''));
end if;
end if;
-- 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-1818389/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29802484/viewspace-1818389/