OraFAQ Forum: Unix » Shell scripts error

Home » Infrastructure » Unix » Shell scripts error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shell scripts error [message #252086]

Tue, 17 July 2007 16:01

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

Report message to a moderator

Re: Shell scripts error [message #252089 is a reply to message #252086]

Tue, 17 July 2007 16:15

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

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

Report message to a moderator

Re: Shell scripts error [message #252340 is a reply to message #252272]

Wed, 18 July 2007 12:06

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??

Report message to a moderator

Re: Shell scripts error [message #252376 is a reply to message #252340]

Wed, 18 July 2007 13:15

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

Report message to a moderator

Re: Shell scripts error [message #252378 is a reply to message #252376]

Wed, 18 July 2007 13:20

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

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...;
/

Report message to a moderator

Previous Topic:	TZ = AEST - AIX Env settings
Next Topic:	assign output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 04:03:33 CST 2024