oracle 仅更改外部表 BADFILE、LOGFILE 和 DISCARDFILE 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7244914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change external table BADFILE, LOGFILE and DISCARDFILE parameters only
提问by Ollie
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.
对于单一格式的文件，通过将表指向新的默认目录和新文件名来重用表。这工作正常，但我现在需要动态更改 BADFILE、LOGFILE 和 DISCARDFILE 参数，同时保持其余访问参数不变。
Is there a direct way to do this without having to respecify all the other access parameters (column transformations filed delimiters etc.) as well?
有没有一种直接的方法可以做到这一点，而不必重新指定所有其他访问参数（列转换文件分隔符等）？
回答by Ollie
Unfortunately changing just the BADFILE, LOGFILE and DISCARDFILE parameters couldn't be achieved without having to respecify the other access parameters too.
不幸的是，如果不重新指定其他访问参数，就无法仅更改 BADFILE、LOGFILE 和 DISCARDFILE 参数。
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.
选择外部表并对其访问参数使用 REGEXP_REPLACE 以使用我提供的新值替换与 BADFILE、LOGFILE 和 DISCARDFILE 及其关联值匹配的访问参数 BLOB 的部分。
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.
然后我使用动态 SQL 来更改外部表并提供新的访问参数。
-- 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.
这并不理想，我最终不得不重新指定所有访问参数，但使用 REGEX（并完全测试输出）意味着该过程不会太痛苦或太慢。
回答by bpgergo
You can change the ACCESS PARAMETERSclause leaving the rest unchanged.
see here http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables013.htm#i1007591Note 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.:
您可以更改ACCESS PARAMETERS条款，其余条款不变。看这里http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables013.htm#i1007591请注意，据我所知，如果你只想改变 BADFILE，你仍然需要重复访问参数中的所有内容。
例如：
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 )
);
回答by Lukasz Szozda
Starting from Oracle 12cR2 you could override parameters for external tables in query.
从 Oracle 12cR2 开始，您可以覆盖 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.
SELECT 语句的 EXTERNAL MODIFY 子句修改外部表参数。
您可以在 EXTERNAL MODIFY 子句中覆盖外部表的以下子句：
默认目录
地点
访问参数
拒绝限制
您可以在一个查询中修改多个子句。可以为 LOCATION 和 REJECT LIMIT 指定绑定变量，但不能为 DEFAULT DIRECTORY 或 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
)
);
