FAQ
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

Search Discussions

  • Nigel Thomas at Dec 7, 2006 at 6:29 pm
    Is there any parameter that we can use during create external table that can overwrite the file?
    Harvinder

    Once the table is dropped, couldn't you just delete the file using UTL_FILE.FREMOVE (http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14163)?

    So:
    - drop table: execute immediate 'drop table '||v_table_name;
    - remove file: UTL_FILE.FREMOVE(p_path, fname);
    - create table: execute immediate v_sql2;

    and remember, if there's no table, there might be no file - so you should handle that exception (whichever one it is - probably DELETE_FAILED, which covers a multitude of sins).

    HTH

    Regards Nigel

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 7, '06 at 5:02p
activeDec 7, '06 at 6:29p
posts2
users2
websiteoracle.com

2 users in discussion

Nigel Thomas: 1 post Harvinder Singh: 1 post

People

Translate

site design / logo © 2022 Grokbase