Inserting into a Oracle table from a shell variable storing thousands of values
1
vote
1
answer
2320
views
I'm trying to insert records into a column in Oracle where the values are passed from a shell variable. The variable itself stores pattern generated from a list of files. This pattern is generated on each iteration inside a for loop.
The variable values are each inserted into the table inside the for loop. But since the variable reads only one record at a time, it inserts only one row at a time. There are thousands of files being read.
The code containing both commands which is ran inside sqlplus and the shell commands is ran as a function. Please find the below for the code:
function call_HEAD_INSERT
{
FILES=/home/oracle/LOG_*.DAT
for f in $FILES
do
#echo "Processing $f file..."
# take action on each file. $f store current file name
ptrn=grep "HEAD" $f
#echo $ptrn
echo "set feedback off;
set heading off;
set serveroutput on size unlimited;
VARIABLE GV_return_code NUMBER;
VARIABLE GV_script_error CHAR(255);
EXEC :GV_return_code := 0;
WHENEVER SQLERROR EXIT 1
DECLARE
L_error_message VARCHAR2(255);
BEGIN
insert into user.customer(HEAD) values ('$ptrn');
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
:GV_return_code := 1;
:GV_script_error := SQLERRM;
END;
/
print :GV_script_error;
exit :GV_return_code;
/" | sqlplus -s ${ORACLE_LOGIN} >> ${logFile}
done
return $?
}
Is there a way to speed up the script or query? I tried giving parallel hint to the query but that didn't really speed up the process.
So is there any other way this process can be improved in speed either in script or inside PL/SQL? Ideally I would love to insert all the records in one go while here on each iteration the insert statement adds only one record.
Any kind of suggestions are welcome.
Asked by AntiNational
(13 rep)
Jun 28, 2020, 02:45 PM
Last activity: Jun 28, 2020, 06:05 PM
Last activity: Jun 28, 2020, 06:05 PM