sqlldr - load completion not reflected

Question

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

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.

Answer 1

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.