p_getusertabdata

create or replace procedure p_getusertabdata(i_user in varchar2)
is
-- fhandle utl_file.file_type;
-- v_line varchar2(4000);
 v_user varchar2(30);
 v_table varchar2(30);
 v_oner varchar2(30);
 v_line varchar2(4000);
 cursor cur_tab is
  select xt.owner,xt.table_name from j1_ldm.xt_tabqd xt where xt.owner=v_oner and (xt.bfl like '2%' or xt.bfl like '4%') and (xt.bbbz like '1%' or xt.bbbz like '2%');
 fhandle utl_file.file_type;
begin
 v_oner :=upper(trim(i_user));
-- fhandle := utl_file.fopen('J1_CXTJ_FILE_DIR','init-object.sql', 'w');
 for cur in cur_tab loop
  v_user :=cur.owner;
  v_table :=cur.table_name;
  begin
   p_gettabdata(v_user,v_table);
  exception
   when others then
    DBMS_OUTPUT.PUT_LINE('BLOB ERRORS:'||v_user||'.'||v_table);
  end;
  DBMS_OUTPUT.PUT_LINE(v_user||'.'||v_table);
--  v_line :=  '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
--  utl_file.put_line(fhandle ,v_line);
 end loop;
-- utl_file.fclose(fhandle);
 fhandle := utl_file.fopen(v_oner||'_FILE_DIR','init-data.sql', 'w');
 for cur in cur_tab loop
  if v_oner='J1_G3_ZBQ' then
     --lvc_line :=  '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
     v_line :=  '@"01 dw\01 inf\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_LDM' then
     v_line :=  '@"01 dw\02 ldm\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_DW' then
     v_line :=  '@"01 dw\03 dw\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_DI' then
     v_line :=  '@"01 dw\05 di\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_CXTJ' then
     v_line :=  '@"03 xt\01 cxtj\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_DMT' then
     v_line :=  '@"03 xt\06 dmt\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_METAONE' then
     v_line :=  '@"03 xt\07 metaone\02 data\01 init\'||cur.table_name||'.sql";';
  elsif v_oner='J1_LDCX' then
     v_line :=  '@"01 dw\08 ldcx\02 data\01 init\'||cur.table_name||'.sql";';     
  end if;
   --@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
     utl_file.put_line(fhandle ,v_line);
  end loop;
  --9、關閉檔案。
  utl_file.fclose(fhandle);  
 exception
  when others then
   DBMS_OUTPUT.PUT_LINE('ERROR');
end;