1

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.

1 Answer 1

0

One option would be to build a list of insert statements, and run sqlplus once with the entire list, something like:

function call_HEAD_INSERT
{
    local files=/home/oracle/LOG_*.DAT
    local insertStatement=""

    # Loop over all files and build a single insertStatement containing the
    # the necessary content from each file
    for f in ${files}; do
        local ptrn="$(grep HEAD "${f}")"
        insertStatement="${insertStatement}
insert into user.customer(HEAD) values ('$ptrn');"
    done

        # Run sqlplus once with all the inserts
        cat <<- EOF | sqlplus -s "${ORACLE_LOGIN}" >> "${logFile}"
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
    ${insertStatement}
    commit;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        :GV_return_code := 1;
        :GV_script_error := SQLERRM;
END;
/

print :GV_script_error;
exit :GV_return_code;
EOF
}

I don't know Oracle's SQL, but there's probably also a way to insert multiple rows with a single insert statement.

3
  • I'm getting this error: Input truncated to 7499 characters SP2-0027: Input is too long (> 2499 characters) - line ignored. Seems like an SQLPLUS error. Is there a way to add line breaks to the output? Jun 28, 2020 at 18:00
  • @AntiNational I edited my answer so that ${insertStatement} now includes line breaks. That should prevent the line from being too long. Jun 28, 2020 at 18:06
  • Thank you so much. It worked. The script now finishes in couple of seconds. Jun 28, 2020 at 18:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .