Calling SQLPLUS from KSH Script 1

[prismtx]

Moving a system from Oracle 9g using unix to Oracle 10g using SUSE Linux. This script works fine on the old system, but gets an "unexpected end-of-file" under the new setup. If I enter the lines individually from a command prompt, it works.

Any ideas on what its seeing as an error?

#!/bin/ksh
#--------------------------------------------
export ORACLE_HOME=/opt/oracle/product/10.2.0.2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SHLIB_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
LOGFILE=/export/home/script.log
USER=USER
PASSWD=PASSWORD
DBNAME=dbname
($ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect ${USER}/${PASSWD}@${DBNAME}
set feedback on
update dbtable d
set d.isflag = null
where
d.isflag is not null;
commit;
exit
EOF
) | tee $LOGFILE
exit 0

 

[commatom]

I ran into a lot of these types of errors when moving from UNIX to LINUX.

I am assuming that you are getting a shell error and not an Oracle error, is that correct?

I ended up storing all my SQL in a *.sql file and calling it from the shell.

Let me know if that helps.

 

[hoinz]

Afaik this error message will come up if there is something wrong with the terminating EOF. It might not start in the first column, or there might be an extra blank behind it in the same line. So please check if something went wrong during the copy from old to new system.
And besides that, I agree with commatom that using a *.sql file will help to avoid a lot of possible problems.

 

[prismtx]

OK, I found an extra character behind the EOF and got that problem fixed, but now I can't login within the script. It says /bin/sqlplus: not foundopt/oracle/product/10.2.0.2

I'm going to contact my DBA to make sure that Oracle is set up correctly. I can log into sqlplus from the command line, just not the script. And I need to login within the script to execute any *.sql files don't I?

 

[commatom]

Check your profile setup, you probably need to run the Oracle environment setup at the beginning of the script

 

[DrD123]

Hi prismtx,

Maybe your ORACLE_HOME isn't the same in the script as in your env.

At a prompt run echo $ORACLE_HOME

Then see what you get.

Also you can try which sqlplus to make sure your bin is set correctly from your env and script.

Good Luck
DrD

 

[prismtx]

I finally found the problem. A learning experience for me!

I had ftp'd the script from a Unix machine to my windows pc and made the changes there. Then ftp'd it to the SUSE Linux machine. I edited the #!/bin/ksh line in vi editor, but apparently the vi editor for SUSE linux, recognizes that the code was edited in DOS and switches to a DOS mode for any editing that I did. It was keeping a carriage return character (cr) from DOS on the line and that was causing the shell to look for a directory called ksh(cr), which it couldn't find. The same problem was on my sqlplus line.

I ran it thru a tr command and now all is working.

Thanks for all who helped!

 

[KenCunningham]

prismtx - a star for posting your solution. Your learning experience may well help others in a similar jam in the future.

I want to be good, is that not enough?

 

[commatom]

Been there, done that. What a pain!

Glad you got it worked out.