When managing External Tables, there should be an easy way to redefine the external source file. It is enough to change the External Table properties by means of an ALTER TABLE command. Let's create a stored procedure that performs this task by means of a dynamically generated DDL command. This procedure, named Change_External_Table redefines the location property. Using a stored program unit is a flexible way to perform this task.

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 ;
/