Change external table BADFILE, LOGFILE and DISCARDFILE parameters only

Question

I have several external tables used to read file data into the DB each one for a particular file specification.

For files of a single format a table is reused by pointing it at a new default directory and new file name. This is working fine except I now need to dynamically change the BADFILE, LOGFILE and DISCARDFILE parameters whilst keeping the rest of the access parameters unchanged.

Is there a direct way to do this without having to respecify all the other access parameters (column transformations filed delimiters etc.) as well?

score 4 · Accepted Answer · answered Sep 20 '11 at 09:43

Unfortunately changing just the BADFILE, LOGFILE and DISCARDFILE parameters couldn't be achieved without having to respecify the other access parameters too.

For what it's worth and for anyone who finds this question in the future i eventually worked around the problem with the following:

Select the external table and use REGEXP_REPLACE on its access parameters to replace the parts of the access parameter BLOB that matched BADFILE, LOGFILE and DISCARDFILE and their associated values with the new values that I supplied.

  CURSOR external_table_cur(
     cp_external_table IN VARCHAR2,
     cp_new_log_dir IN VARCHAR2,
     cp_log_file IN VARCHAR2
  )
  IS
     SELECT table_name,
            REGEXP_REPLACE(
               access_parameters,
               <REGEX PATTERN>,
               cp_new_log_dir||':'''||LOWER(cp_log_file),
               1,
               0,
               'i'
            ) AS new_access_params
       FROM all_external_tables
      WHERE table_name = UPPER(cp_external_table);

I then used dynamic SQL to alter the external table and supplied the new access parameters.

  -- Point external table to new file, directory and access params
  EXECUTE IMMEDIATE(
     'ALTER TABLE '
     || p_table_name
     || ' DEFAULT DIRECTORY '
     || p_directory
     || ' LOCATION ('''
     || p_filename
     || ''') '
     || ' ACCESS PARAMETERS ('
     || TO_CHAR(new_access_params)
     || ')'
  );

It's not ideal and I did end up having to respecify ALL the access parameters but using the REGEX (and fully testing the output) meant the process wasn't too painful or slow.

score 2 · Answer 2 · answered Jun 15 '18 at 12:35

Starting from Oracle 12cR2 you could override parameters for external tables in query.

The EXTERNAL MODIFY clause of a SELECT statement modifies external table parameters.

You can override the following clauses for an external table in an EXTERNAL MODIFY clause:

DEFAULT DIRECTORY

LOCATION

ACCESS PARAMETERS

REJECT LIMIT

You can modify more than one clause in a single query. A bind variable can be specified for LOCATION and REJECT LIMIT, but not for DEFAULT DIRECTORY or ACCESS PARAMETERS.

For example:

SELECT *
FROM tab_ext EXTERNAL MODIFY (
                 ACCESS PARAMETERS (
                   BADFILE temp_dir_2:'some_tab_ext_%a_%p.bad'
                   LOGFILE temp_dir_2
                   DISCARDFILE temp_dir_2
                 )
              );

score 0 · Answer 3 · answered Aug 30 '11 at 14:45

0

You can change the ACCESS PARAMETERS clause leaving the rest unchanged. see here http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables013.htm#i1007591 Note that as far as I know, If you want to change only the BADFILE, you still have to repeat all things in the ACCESS PARAMETERS.
e.g.:

ALTER TABLE ext_table
  ACCESS PARAMETERS
   (
     records delimited by newline 
     badfile admin_bad_dir:'empxt%a_%p.bad' 
     logfile admin_log_dir:'empxt%a_%p.log' 
     fields terminated by ',' 
     missing field values are null 
     ( field1, field2 ) 
  );

answered Aug 30 '11 at 14:45

bpgergo

15,669
5
44
68

I realise you can dynamically change the access parameters but as my question asks, I need a method whereby I change the BADFILE, LOGFILE etc without having to respecify the other parameters. I'm thinking this cannot be done. – Ollie Aug 30 '11 at 15:05
I see. Well it can be done, but you have to dynamically recreate the original `ACCESS PARAMETERS` clause from the metadata (http://en.wikipedia.org/wiki/Oracle_metadata). Should it seem to be difficult, I suggest to open a new thread on how to to do that. After you recreated the ACCESS PARAMETERS clause, you replace the BADFILE AND THE LOGFILE, then run the ALTER TABLE. – bpgergo Aug 30 '11 at 15:17
It's not that it's difficult to get the metadata for the access parameters its more that it is potentially introducing bugs if i have to manipulate all of the access parameters just to change a few of their values, especially considering there could be a large number of access parameters for fixed-width files. I'm thinking that I may have to approach the problem from another angle. – Ollie Aug 30 '11 at 15:35

Change external table BADFILE, LOGFILE and DISCARDFILE parameters only

3 Answers3