Sample Header Ad - 728x90

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