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 endedOn running the above script, we get the output:
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 endedAs 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 endedThe 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.