sqlldr-加载完成不反映 [英] sqlldr - load completion not reflected

问题描述

我有一个bash脚本( load_data.sh )，该脚本调用sqlldr命令将数据从.csv加载到表中( data_import ).在最近的一次调用中，我注意到即使命令执行完成，该表也不包含来自.csv文件的数据.我之所以这样说，是因为bash脚本中的后续语句( process_data.sh )试图运行引发错误的存储过程.
ORA-01403:未找到数据.

I have a bash script (load_data.sh) that invokes the sqlldr command to load data from a .csv into a table (data_import). On a recent invocation, I noticed that even though the command execution was completed, the table didn't contain the data from the .csv file. I say this because the subsequent statement (process_data.sh) in the bash script tried to run a stored procedure that threw the error
ORA-01403: no data found.

我了解，该提交是在文件加载后立即进行的.因此，我想知道是什么导致了此错误，以及将来如何避免该错误.

I learned that the commit happens right after the file load. So, I'm wondering what's causing this error and how I can avoid it in the future.

这是我的脚本:

#!/usr/bin/bash -p

# code here #

if [[ -f .st_running ]]
then
echo "Exiting as looks like another instance of script is running"
exit
fi

touch .st_running

# ... #

# deletes existing data in the table
./clean.sh

sqlldr user/pwd@host skip=1 control=$CUR_CTL.final data=$fpath log=${DATA}_data.log rows=10000 direct=true errors=999

# accesses the newly loaded data in the table and processes it
./process_data.sh

rm -f .st_running

clean.sh/process_data.sh

# code here #
# ... #

sqlplus user/pwd@host  <<EOF
set serveroutput on
begin
schema.STORED_PROC;
commit;
end;
/
exit;
EOF

# code here #
# ... #

STORED_PROC 由process_data.sh运行:

STORED_PROC run by process_data.sh:

SELECT count(*) INTO l_num_to_import FROM data_import;

IF (l_num_to_import = 0) THEN RETURN;
END IF;

/* the error (`ORA-01403: no data found`) happens at this statement: */
SELECT upper(name) INTO name FROM data_import WHERE ROWNUM = 1;

控制文件

LOAD DATA
APPEND
INTO TABLE DATA_IMPORT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
...
...
)

编辑

1. 输入文件有8行，两次运行的日志都表明已成功插入8行.
2. 有趣的行为:第二次在同一文件上运行脚本时，脚本运行良好(无需抱怨错误).因此，在第一次运行期间，在执行下一个sqlplus命令之前，sqlldr命令似乎没有完成.

1. The input file had 8 rows and the logs from both runs stated that 8 rows were successfully inserted.
2. Interesting behavior: The script ran fine (without complaining about the error) the 2nd time I ran it on the same file. So, during the first run, the sqlldr command doesn't seem to complete before the next sqlplus command is executed.

推荐答案

如果捕获sqlldr命令的PID并等待其完成，那么您将确定它已完成.您可以将日期时间戳记添加到日志文件或时间戳记(如果它每天运行多次)，并进行一次while循环和休眠，并检查日志何时打印其最后一行.然后运行下一步.

If you capture the PID of the sqlldr command and wait for it to complete then you will be sure its complete. You can add a datestamp to the log file or timestamp if its run multiple times a day and do a while loop and sleep and check to see when the log prints its last line of completion. Then run the next step.

这篇关于sqlldr-加载完成不反映的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！