Insert to Oracle table in a loop

Question

Requirement: In one server in a directory multiple files are present. I need to read the file names and populate into a Oracle table.

I have written the script like this, but its not working:

#!/bin/bash
names = find  /home/devuser -name 'BI*'
sqlplus -s  schema_name/passwd << EOF
for name in {names[@]}
do
insert into table1(file_name,status) values('$name','N'); done commit; exit

It's trowing DO INVALID IDENTIFIER.

Solution 1

for VARIABLE in 1 2 3 4 5 .. N
do
    command1
    command2
    commandN
done <<anything given here is taken as variable>>

This is the basic syntax and after done, commit should be given in next line.

So your code should be.

#!/bin/bash
names = find  /home/devuser -name 'BI*'
sqlplus -s  schema_name/passwd << EOF
for name in {names[@]}
do
insert into table1(file_name,status) values('$name','N'); 
done 
commit; 
exit

Solution 2

Here's a version of the script that works, and fixes some things you may not have considered:

#!/bin/bash
(
    echo '-- some startup SQL*plus code'
    echo 'set echo off;'
    echo '-- etc'
    find /home/devuser -name 'BI*' -type f |
    awk -- '{
                gsub("\047", "\047\047", $0);
                printf("INSERT INTO table1(file_name,status) " \
                       "VALUES(\047%s\047, \047N\047);\n", $0);
            }'

    echo 'COMMIT;'
) | sqlplus -s schema_name/passwd

Features:

• Selects only files (-type f in find) matching the glob ‘BI*’ — see the manpage for find(1) for the different file types available.
• Escapes filenames containing single quotes ('→'') so the output is valid SQL. \047 is the octal ASCII code of the single quote, useful since the awk code is inside single quotes already.
• Faster than backtick expansion.
• Leave out the | sqlplus ... part of the last line and you can see the SQL script generated.

Limitations:

• Does not work with filenames containing newlines or any other non-printable characters.
• May not work with non-ASCII characters. This depends on a number of factors, some on the POSIX side, some on the Oracle side (and some likely inside SQL*plus itself).
• It's not necessarily the best way (better people will no doubt chime in with their versions).

The groupped section in the beginning generates the script, including a prologue (useful for telling SQL*plus what to do with some of its ‘nicer’ features) and an epilogue to commit the transaction.

What's wrong with your own version of the script:

• Assignments are like this: VAR=foo. Not VAR = foo (yes, it matters; the latter will try to run command VAR with arguments = and foo, not perform a variable assignment)
• If you want $names to expand to the results of the find command, you need to say names=$(find ...) or names=`find ...`, not names=find ....
• Anything after the <<EOF is sent to SQL*Plus, so the bash script fragment you're including (with the for loop) is interpreted by SQL*Plus, not bash. That isn't valid SQL, and SQL*Plus naturally chokes on it.
• The body of the for loop is an SQL statement, but you're expecting this to be interprered by bash, which isn't right. There should be an echo before the SQL insert statement (and of course the entire thing out of the <<EOF section).
• You've left out the EOF itself. <<EOF means ‘read until a line is encountered that starts with EOF’.