here-doc(EOF) issue in sqlplus or FTP

   In one of our articles, we had seen the different ways in which we can connect to sqlplus from Unix. The first way discussed was using the heredoc or the here-document method(EOF). Many times users encounter a weird issue while using the here-doc in sqlplus or FTP. The issue is no statements after the closing EOF get executed .

  Let us take an example of an update query inside sqlplus:

#!/usr/bin/ksh
echo script started
sqlplus -s guru/unix11@XE <<EOF         
     UPDATE EMP SET FNAME='BLOGGER' WHERE EMPID=10;
     COMMIT;
     exit
     EOF
echo script ended 

 On running the above script,  we get the output:

script started

         The "script ended" print statement is missing. The sqlplus part works without any issue. However, the commands after the closing EOF does not get the control and hence the "echo" statement at the end does not get executed.  In other words, the control never reached the code after the closing EOF. A careful look at the script does not indicate any error at all. In fact, there is no error, just that we need to take care of the here-doc conventions.

2 ways to rectify this:

1.  Whenever you use here-doc(EOF),  make sure the closing here-doc should be at the beginning of the line.

#!/usr/bin/ksh
echo script started
sqlplus -s guru/unix11@XE <<EOF         
     UPDATE EMP SET FNAME='BLOGGER' WHERE EMPID=10;
     COMMIT;
     exit
EOF
echo script ended

   As above, the closing EOF is put in the beginning of the line. On running this script now, we get:

script started
script ended

2.  Sometimes, you do not want to put the closing EOF at the beginning of the line just because your alignment gets spoiled. The other way to fix this is to put an (-) before the here-doc(EOF). And this should be matched by putting a tab-space before the closing EOF. 

#!/usr/bin/ksh
echo script started
sqlplus -s guru/unix11@XE <<-EOF         
     UPDATE EMP SET FNAME='BLOGGER' WHERE EMPID=10;
     COMMIT;
     exit
     EOF
echo script ended

     The closing EOF above is preceeded by a tab-space, not emtpy spaces.

Note: It is not mandatory to use the word EOF after the here-doc operator(<<). You can use any word of your choice. Whatever you use, make sure to use the same for the closing one as well.

Tweet