dbms_file_transfer.copy_file example - generation script

-- ---------------------------------------
--
-- dbms_file_transfer.copy_file example for a Data Guard broker file
--
-- NOTE: The Data Guard configuration must be disabled to allow this to happen
-- and also stop DMON process by doing alter system set dg_broker_start='false';
--
-- ---------------------------------------

set serveroutput on format wra

declare
  uname    varchar2(50);
  bfile    varchar2(50);
   cursor C1 is
    select lower(value) uname from v$parameter where name = 'db_unique_name';
   cursor C2 is
    select SUBSTR( value, INSTR( value, '/', -1 ) + 1, LENGTH( value )) bfile from v$parameter where name =

'dg_broker_config_file1';
begin
for x in C1 loop
 dbms_output.new_line;
 dbms_output.put_line('CREATE OR REPLACE DIRECTORY SOURCE_DIR AS ''+DATA01/'||x.uname||''';');
 dbms_output.new_line;
end loop;
for y in C2 loop
 dbms_output.put_line('BEGIN');
 dbms_output.put_line('dbms_file_transfer.copy_file(source_directory_object =>');
 dbms_output.put_line('''SOURCE_DIR'', source_file_name => '''||y.bfile||''',');
 dbms_output.put_line('destination_directory_object => ''DP_DUMP'',');
 dbms_output.put_line('destination_file_name => ''/tmp/'||y.bfile||''');');
 dbms_output.put_line('END;');
 dbms_output.new_line;
 dbms_output.put_line('drop directory SOURCE_DIR;');
 dbms_output.new_line;
end loop;
end;
/