3 Replies Latest reply: Mar 30, 2007 9:43 AM by 541781 RSS

    alter table (external table) location?

    541781
      Hi,

      I'm using this statement to change the filename for an external table definition, this works fine.
      Is it possible to alter the external table location also and make it dynamicly and not only the filename?

      alter table '||ext_table_name||' location('''||filename||'.csv'||''')';

      Filename is a parameter in my procedure.

      Thanks in advance.
      CarolineB
        • 1. Re: alter table (external table) location?
          Kaushik_Orcl
          Caroline,

          I think it is possible. You can try general syntax:
          ALTER TABLE [schema.]external_table_name location (new_directory_name:'new_filename.csv');

          I have not tried though. Let me know if it was successfull.

          HTH,
          Kaushik.

          P.S Link for syntax: http://www.ss64.com/ora/table_a_external.html
          • 2. Re: alter table (external table) location?
            567749
            Hi Caroline,

            It is possible and I was looking at doing the same thing, as I need to load multiple files with different file names using an external table. At first it seemed like a good idea, but I came across this lengthy, but interesting and informative discussion on the ask tom forums, where they advised against it.

            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37593123416931

            Have a look, but depending on the situation and the application in which you are using the alter command to change the external table to accommodate different files it might end up creating complications.

            Jurgen
            • 3. Re: alter table (external table) location?
              541781
              Thanks it works :

              stmnt:= 'alter table '||ext_table_name||' location('||ff_module||':'||''''||filename||'.csv'||''')' ;

              execute immediate stmnt;

              CarolineB