Home » Infrastructure » Unix » Shell scripts error
Shell scripts error [message #252086] Tue, 17 July 2007 16:01 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

In this script i am trying to run some sql job. I have create one for loop. I have problem, the variable i which is i created, it is keep moving around. Is anybody correct this script, so it will run the sql file only once, after run it should exit.

Thanks in advance

# Set environment.
# ----------------
PGM_DIR=$HOME/gss
LOG=$PGM_DIR/log.`date "+%m%d%y%H%M%S"`
OUSER="lc_monitor"
OPASS="lcm786"
DBN="lnlcsuu1"


#------------------------
# Define Functions
#------------------------

Run_SQL()
{
sqlplus $OUSER/$OPASS@$DBN @$i
}

GSS_PGM_LIST=" \
ORDER_ASSIGNMENT_NEW \
Copy_assignment_new \
Cop1_assignment_new \
- \
"

# programs to run.
# -----------------------------
PGM_LIST="$GSS_PGM_LIST"

# ------------
# Main program
# ------------

echo "\n\n======= $0 Start ($TWO_TASK) at `date`  =======" >> $LOG 2>&1


for i in $PGM_LIST
do
    if [ "$i" != "-" ]
    then
        cd `dirname $PGM_DIR/$i`
        Run_SQL `basename $i`  >> $LOG 2>&1
    else
         if [ "$i" == "-" ]
            then
              echo "$1 LOADING ERROR: an error occurs while loading fileE" >> $LOG
        fi
    fi
done


Re: Shell scripts error [message #252089 is a reply to message #252086] Tue, 17 July 2007 16:15 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Use EXIT and EXIT 0 at the end of script.

use it as example it exit from sqlplus as well as as oracle user
#!/bin/ksh -x
# make env file avail to this script
. $HOME/.profile_DB
$ORACLE_HOME/bin/sqlplus /NOLOG <<HERE
-- Exit with failure, if SQL, PL/SQL or OS error is raised in top level proc
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
CONNECT abc/abc2005
Spool /export/home/oracle/activity.sql
Drop  table MYTABLE purge;
Spool off
Exit
Exit 0

[Updated on: Tue, 17 July 2007 16:16]

Report message to a moderator

Re: Shell scripts error [message #252272 is a reply to message #252089] Wed, 18 July 2007 07:59 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

I really appreciate your help. But the problem i explain you again
GSS_PGM_LIST=" \
ORDER_ASSIGNMENT_NEW \
-\
"

# programs to run.
# -----------------------------
PGM_LIST="$GSS_PGM_LIST"

# ------------
# Main program
# ------------

echo "\n\n======= $0 Start ($TWO_TASK) at `date`  =======" >> $LOG 2>&1


for i in $PGM_LIST
do
    if [ "$i" != "-" ]
    then
        cd `dirname $PGM_DIR/$i`
        Run_SQL `basename $i`  >> $LOG 2>&1
    else
         if [ "$i" ==  "-" ]
            then
               exit 0
        fi
    fi
Done



The problem is this It is giving this error. First it creates the table(table Created), Then i don't know why it again run the script(ERROR at line 1:).

Table created

CREATE TABLE JP_FACE_RAW_TDALLOCATION_NEW
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Re: Shell scripts error [message #252340 is a reply to message #252272] Wed, 18 July 2007 12:06 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Then i don't know why it again run the script(ERROR at line 1:).


when you run again this script does this script first drop that table and recreate??
Re: Shell scripts error [message #252376 is a reply to message #252340] Wed, 18 July 2007 13:15 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

This script does not drop. I drop it manually before i run this
script.

Thanks alot
Re: Shell scripts error [message #252378 is a reply to message #252376] Wed, 18 July 2007 13:20 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You working in ksh??

omzesp01DS:/export/home/oracle$
>cat test.ksh
#!/bin/ksh -x
# make env file avail to this script
. $HOME/.profile_ds
$ORACLE_HOME/bin/sqlplus /NOLOG <<HERE
-- Exit with failure, if SQL, PL/SQL or OS error is raised in top level proc
WHENEVER SQLERROR EXIT FAILURE;
WHENEVER OSERROR EXIT FAILURE;
CONNECT oracle/dsoracle
Spool /export/home/oracle/activity.sql
Create table test
As
Select * from dba_users;
Spool off
Exit
Exit 0



omzesp01DS:/export/home/oracle$
>. test.ksh

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jul 18 18:18:48 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> SQL> SQL> SQL> Connected.
SQL> SQL>   2    3
Table created.

SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

[Updated on: Wed, 18 July 2007 13:21]

Report message to a moderator

Re: Shell scripts error [message #252425 is a reply to message #252378] Wed, 18 July 2007 15:32 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
make sure that your sql script runs in isolation without error.

if you have both ";" and "/" at the end of a statement, it will run twice.

CREATE TABLE JP_FACE_RAW_TDALLOCATION_NEW...;
/
Previous Topic: TZ = AEST - AIX Env settings
Next Topic: assign output
Goto Forum:
  


Current Time: Sun Dec 11 04:11:02 CST 2016

Total time taken to generate the page: 0.09532 seconds