oracle 可以通过 sqlldr 命令将变量传递给 SQL*Loader 控制文件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21278726/
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
Can variables be passed to a SQL*Loader control file via sqlldr command?
提问by arul.k
Below is my Control file example :
下面是我的控制文件示例：
OPTIONS (skip=1,errors=1000,direct=true,rows=10000)
load data
append
into table TABLE_NAME
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols(
DATE_ID DATE_ID_VALUE,
DESC1 char(1000),
DESC2 char(1000),
DISP_URL char(1000),
DEST_URL char(1000),
ACCT_ID ACCOUNTID_VALUE,
Acct_num ACCOUNT_NUM,
created_date SYSDATE
)
I need to pass DATE_ID_VALUE,ACCOUNTID, ACCOUNTNUM values from sqlldr command. Am passing the remaming columns data via csv file which inturn is passed from sqlldr "DATA" parameter. Is there a way to pass other required parameters via sqlldr command or any other way to do it?
我需要从 sqlldr 命令传递 DATE_ID_VALUE、ACCOUNTID、ACCOUNTNUM 值。我通过 csv 文件传递 remaming 列数据，该文件又是从 sqlldr "DATA" 参数传递的。有没有办法通过 sqlldr 命令或任何其他方式传递其他必需的参数？
Below is my sqlldr command :
下面是我的 sqlldr 命令：
sqlldr userid=abc/abcdef@abcdefgh CONTROL= cont.ctl DATA= $csvFilePath LOG=admaster.log BAD=admaster.bad
回答by Gary_W
I know this is old, but I just stumbled on it and I recently answered a similar question. Please see my reply here for a technique to create a control file from a wrapper program.
我知道这是旧的，但我只是偶然发现它，我最近回答了一个类似的问题。请参阅我在此处的回复，了解从包装程序创建控制文件的技术。
回答by Durban_legend
It is not possible to parameterise the variables in the .ctl file, but it is possible to dispense with a .ctl file altogether and parameterise whatever you want.
无法在 .ctl 文件中参数化变量，但可以完全省去 .ctl 文件并根据需要参数化任何内容。
Rather than calling sqlldr directly, you need to declare an external table, then do a SQL INSERT into TABLE_NAME SELECT * from EXTERNAL_TABLE;. The external table declaration contains the sqlldr parameters that are used behind the scenes, and is defined in a SQL query which can be run directly from the Unix shell, with all the required parameters specified as Unix system variables or commands.
而不是直接调用 sqlldr，您需要声明一个外部表，然后执行 SQL INSERT into TABLE_NAME SELECT * from EXTERNAL_TABLE;。外部表声明包含在幕后使用的 sqlldr 参数，并在可以直接从 Unix shell 运行的 SQL 查询中定义，所有必需的参数都指定为 Unix 系统变量或命令。
E.g. having set the values of $DATE_ID_VALUE and the other 2 variables in your calling environment, first create an external table:
例如，在调用环境中设置了 $DATE_ID_VALUE 和其他 2 个变量的值后，首先创建一个外部表：
echo "create table myschema.temp_table_name (
DATE_ID INTEGER,
DESC1 char(1000),
DESC2 char(1000),
DISP_URL char(1000),
DEST_URL char(1000),
ACCT_ID INTEGER,
Acct_num INTEGER,
created_date DATE)
organization external
(
type oracle_loader
default directory mydir
access parameters (
records delimited by newline
badfile bad_dir: 'temp_ext_temp_table_name_load.bad'
logfile log_dir: 'temp_ext_temp_table_name_load.log'
fields terminated by ',' (
DESC1 char(1000),
DESC2 char(1000),
DISP_URL char(1000),
DEST_URL char(1000)
)
column transforms (
DATE_ID FROM CONSTANT '$DATE_ID_VALUE',
ACCT_ID FROM CONSTANT '$ACCOUNTID_VALUE',
Acct_num FROM CONSTANT '$ACCOUNT_NUM',
created_date FROM CONSTANT \"`date '+%d-%b-%Y'`\"
)
)
location ('temp_table_name.dat')
)
reject limit 1000;" | sqlplus -s /
The column transformsclause will populate the external table with the constant values resolved from your environment variables and the Unix date command.
该column transforms子句将使用从环境变量和 Unix date 命令解析的常量值填充外部表。
Then do the insert into the target table (optional appendhint for direct path load):
然后插入目标表（append直接路径加载的可选提示）：
insert /*+ append */ into table_name
select * from myschema.temp_table_name;
I couldn't find a way to include SYSDATE, so used the Unix date command equivalent instead.
我找不到包含 SYSDATE 的方法，所以改用等效的 Unix date 命令。
