Oracle Utilities - Dynamically Changing the External Reference

Author: Packt Publishing Published on: 19th Jan 2010

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.

Ads

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.

Sample Code

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
/

Ads

Oracle 11g External Table enhancements

External Tables work the same in 10g and in 11g, so there are no differences when working with these two versions. When working with Data Pump External Tables, and one single row proves defective, the data set reading operation is aborted. An enhancement in this 11g release prevents the data load aborting, thus saving reprocessing time.

Read Next: Oracle Utilities - Summary

This tutorial is part of a Oracle Database Management Utilities tutorial series. Read it from the beginning and learn yourself.

Oracle Database Management Utilities