How to call an sql script inside a while statement in KSH

Shell Programming and Scripting
1

Hi all,

I'm trying to run an sql inside a loop which looks like this

#!bin/ksh
while IFS=, read var1 var2 
do

 sqlplus -s ${USERNAME}/${PASSWORD}@${ORACLE_SID} << EOF
    insert into ${TABLE}
        (
        appt_date
        )
  values
        (
        '${var1 }'
        );
    COMMIT;
    exit;
    EOF

done < "$file"

An error pop out and tells:

sh: Syntax error: '<<' is not matched.

Does anyone knows how to fix this error to be able to run the sql script successfully? Need help.

2

If you post code, please surround it with

```text
```

tags, either by entering them manually, or by selecting the code and clicking the # button at the top of the edit box.

With a heredoc (the '<<' stuff), the ending token (in your case 'EOF') has to start at the first column on a line of it's own. Remove the leading whitespace in that line, and it should be fine.

3

Hi pludi,

thanks for replying, i do what you had said giving this input:

sqlplus -s ${USERNAME}/${PASSWORD}@${ORACLE_SID} <<EOF

but the same error still occurs

4

Read the post again. I said the ending token has to be the first and only thing on a line of it's own. The ending token is the EOF. Try to spot the difference:

#!bin/ksh
while IFS=, read var1 var2 
do

 sqlplus -s ${USERNAME}/${PASSWORD}@${ORACLE_SID} << EOF
    insert into ${TABLE}
        (
        appt_date
        )
  values
        (
        '${var1 }'
        );
    COMMIT;
    exit;
EOF

done < "$file"
5

The problem is rather simple: you indented your code and indented the "here-document" too, but this is not allowed. When you write "process << EOF" the shell will search for a line starting with " EOF" and interpret everything between your statement and this line as part of the document.

As you have indented your source code you have changed this closing line of your here-document to several spaces and only then the "EOF" and this makes this line invalid. Example:

# will work:
some_process <<EOF
blah blah
EOF

# will not work:
some_process << EOF
blah blah
     EOF

I hope this helps.

bakunin

6

thanks pludi and bakunin. the solution you gave worked :slight_smile:

7

'man ksh' yields:

     << [-]word
           The shell input is read up to a line that is the  same
           as word, or to an EOF. No parameter substitution, com-
           mand substitution, or file  name  generation  is  per-
           formed  on  word.  The  resulting  document,  called a
           here-document, becomes  the  standard  input.  If  any
           character  of  word  is  quoted,  no interpretation is
           placed upon the characters of the document. Otherwise,
           parameter  and command substitution occur, \NEWLINE is
           ignored, and \ must be used to quote the characters \,
           $,  `,  and  the  first  character  of  word.  If - is
           appended to <<, then all  leading  tabs  are  stripped
           from word and from the document.

 will work:
some_process <<EOF
blah blah
EOF

# will work as well:
some_process <<-EOF
blah blah
     EOF