Oracle Datapump question (parameter to overwrite file)

Hi,

We archive the data using external tables(datapump) and works fine but we also allow clients to archive the same data using the procedure. Now when they try to export data secodn time, oracle is throwing error that file with same name already exists (they don't get error on external table since we drop it before creating again). Is there any parameter that we can use during create external table that can overwrite the file. Following is the part of sample script that we currently using:

        v_sql1 := 'SELECT au.* FROM 
                tab1 au
                where au.col1 =' || cast (v_interval as varchar2);
        fname := 'tab1_' || cast (v_interval as varchar2 ) || '.txt';
v_table_name := substr(fname,1,instr(fname,'.')-1);
        if table_exists (v_table_name)
        then
            execute immediate 'drop table ' || v_table_name;
        end if;
        begin
            v_sql2 := 'create table ' || v_table_name || ' 
                ORGANIZATION EXTERNAL
                (
                TYPE ORACLE_DATAPUMP
                DEFAULT DIRECTORY ' || p_path ||
                ' LOCATION (''' || fname || ''')
                ) as ' || v_sql1;
            execute immediate v_sql2;

Thanks
--Harvinder

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 07 2006 - 11:02:25 CST