/* This procedure will enable you to dynamically change the file name
for an external table. Assumed is that the file is in the same directory
(this can be changed, but that would require a small change). The script
works on Oracle 9.2 or higher. To use it, include it in the Warehouse
Builder repository as a transformation and use it in the context of a
process flow or as a pre-mapping process in the mapping editor. The
procedure must be deployed to the target schema. */

create procedure change_external_table
( p_table_name in varchar2
, p_file_name in varchar2
) is
begin

  execute immediate 'alter table '
                    || p_table_name
                    || ' location ('''
                    || p_file_name
                    || ''')' ;

  exception
    when others
    then
      raise_application_error(sqlcode,sqlerrm) ;

end ;
/