Oracle Datapump question (parameter to overwrite file)

  • From: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>
  • To: <Oracle-L@xxxxxxxxxxxxx>
  • Date: Thu, 7 Dec 2006 12:02:25 -0500

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

--
//www.freelists.org/webpage/oracle-l


Other related posts: