Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    35

    Unhappy Unanswered: Why i got error mesage even the process is success

    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.

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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

  3. #3
    Join Date
    Jun 2003
    Posts
    35
    Hi Damian.

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




    Originally posted by Damian Ibbotson
    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

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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?

  5. #5
    Join Date
    Jun 2003
    Posts
    35
    What i meant is if i remove the back tick, it will


    Originally posted by Damian Ibbotson
    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?

  6. #6
    Join Date
    Jun 2003
    Posts
    35
    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.





    Originally posted by msetjadi
    What i meant is if i remove the back tick, it will

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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.

  8. #8
    Join Date
    Jun 2003
    Posts
    35
    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?







    Originally posted by Damian Ibbotson
    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.

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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
    --------------------------------------------------------

  10. #10
    Join Date
    Jun 2003
    Posts
    35
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •