Hi I have a unix shell script that generate execute sqlplus as follows(i include the line number):


99 `sqlplus $s'@'$u <<-EOD
100 Set termout off linesize 500 pagesize0 verify off feedback off;
101 Clear Buffer
102 Clear Columns
103 Spool $opdate/sourcetablecount.log
104 @checknumobj.sql
105 exit;
106 /
107 EOD`


When i run the script, it generates error message as follows:

./migrate.sh[107]: SQL*Plus:: not found



When i took a look at file sourcetablecount.log(my spool file),it can generate the number of record as desired.


Do you guys encounter this problem before? What i expect is Oracle/Unix should not return any error message because it can generate the file accordingly.

Any idea about this problem is appreciated.

Remove the backticks unless you want to substitue the output of your sql into the current shell.

Try

`echo date`

Fri 8 Aug 09:41:14 2003

`echo gubbins`

ksh: gubbins: not found.

In both instances, the echo inside the backticks is doing what you'd expect but the output ('date' and 'gubbins') is then picked up by the shell.

'date' is a commnad and therefore outputs date. 'gubbins' is nonsense and so, outputs an error message.

HTH

Hi Damian.

If i take out the back ticks, it seems the shell interpret "exit;"
which will exit my script directly.

What do you mean?

Does it exit the shell? I can't see why this would happen.

If the shell was interpreting 'exit', your session would close.

Can you post your error?

What i meant is if i remove the back tick, it will

The error are as follows:


./migrate.sh[107]: SQL*Plus:: not found


I need to get rid of this error message.


If i remove the back ticks, my script is ended even my session is not terminated.

Okay. That's your error message when you include the backticks.

Do you get an error message when you remove the backticks or are you just not getting the output you expect?

I'd guess that you probably don't need the 'exit' at all because you are not running SQLPlus interactively. The method you are using will implicitly exit from SQLPlus once it has completed all the actions between EODs. I don't see how its inclusion would affect what you are trying to do though.

Are you expecting the script to end with you in an active SQLPlus session? I'm afraid that's not going to happen.

Hi Damian,

I have removed the backticks and still do not have desired result,because it get out of the code without executing the last script.

If i do not have the exit; it worse, it got error.

For instance the following code(i cat it so it can show the line number).
-----------------------Start of my example---------------------
[shell test]
[shell test]
[shell test]
[shell test] cat -n test.sh
1 #!/bin/sh
2
3
4 echo "I am about to check number of objects in source schema "
5 $ORACLE_HOME/bin/sqlplus TXX/TXX@HXXX <<HERE
6 Set termout off linesize 500 pagesize0 verify off feedback
off;
7 Spool 20030812/srctblcount.log;
8 Select count(*) from user_tables;
9 exit;
10 HERE
11 echo "After cehck number of objects in source, the exit code="
$?
[shell test]
[shell test]
[shell test] ./test.sh
I am about to check number of objects in source schema

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 12 10:57:12 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

SQL> SQL> SQL> 4
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 -
64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
[shell test]
[shell test]
-----------------------------End of my example--------------



It seems that the following last code was never executed:

echo "After cehck number of objects in source, the exit code=" $?


Do you encounter this before?

I really don't understand this behaviour.

Try running the following as a script. Once it has finished, if you 'echo $?' and get 99, you know that the script has run to completion.

It should give an indication of where to look next.


--------------------------------------------------------
set -o xtrace
echo "Starting SQLPlus..."

$ORACLE_HOME/bin/sqlplus TXX/TXX@HXXX <<!!
select count(*) from user_tables;
!!

echo "Finished SQLPlus."

exit 99
--------------------------------------------------------

Hi Damian,

thanks for the tips, the problem was on open and close of "here-document"

I can not have space before closing tag (!!).


Regards,
Michael