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.
Here are my scripts:
load_data.sh
#!/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 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;
Control file
LOAD DATA
APPEND
INTO TABLE DATA_IMPORT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
...
...
)
Edits
- The input file had 8 rows and the logs from both runs stated that 8 rows were successfully inserted.
- 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.
